sql row constructor...works!
Thanks for bringing the SQL 92 row constructor into spec for
operations involving > and <. This is just fantastic. I just
benchmarked ISAM style access to tables on multi-part keys and the
speedup is tremendous vs. the non row-constructor approach to the
problem which is tedius to write and only selective to the first part
of the key. We are talking speedups proportional to the inselectivity
of the ordering key. While this feature is relatively esoteric, it is
powerful and belongs in every dba's bag of tricks.
I was not expecting full indexabilty on the operation but this too
works properly and efficiently. Great work! IMO, most people are not
aware of a properly working row constructor because it is either not
implemented or improperly implemented. Once again, the team has shown
that it can meet or defeat the big name databases on the feature
level.
Merlin
Merlin,
Thanks for bringing the SQL 92 row constructor into spec for
operations involving > and <. This is just fantastic. I just
benchmarked ISAM style access to tables on multi-part keys and the
speedup is tremendous vs. the non row-constructor approach to the
problem which is tedius to write and only selective to the first part
of the key. We are talking speedups proportional to the inselectivity
of the ordering key. While this feature is relatively esoteric, it is
powerful and belongs in every dba's bag of tricks.
It's esoteric enough that I don't know what you're talking about. Can
you give us an example?
--Josh
Merlin Moncure <mmoncure@gmail.com> writes:
Thanks for bringing the SQL 92 row constructor into spec for
operations involving > and <. This is just fantastic.
Thought you'd like that ;-)
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes:
It's esoteric enough that I don't know what you're talking about. Can
you give us an example?
He's talking about this:
http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes:
It's esoteric enough that I don't know what you're talking about. Can
you give us an example?He's talking about this:
http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php
Hmm...
So the example would be that if you have just read a record from t
that gave you value t.a=a1, t.b=b1, t.c=c1, the next one, based on
a,b,c, would be...
select * from t where a >= a1 and b >= b1 and c >= c1
order by a,b,c
limit 1 offset 1;
Right?
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/lsf.html
"It's not about 'Where do you want to go today?'"; "It's more like,
'Where am I allowed to go today?'"
He's talking about this:
http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.phpHmm...
So the example would be that if you have just read a record from t
that gave you value t.a=a1, t.b=b1, t.c=c1, the next one, based on
a,b,c, would be...select * from t where a >= a1 and b >= b1 and c >= c1
order by a,b,c
limit 1 offset 1;Right?
Wrong! :) Don't feel bad, no one ever gets this right the first time,
including me! If you use values 2,2,2 for a1,b1,c1, the query will
not return (3,1,2)...you have to look at the key as a whole instead if
specific fields.
The proper SQL construct without row constructor is:
select * from t where
a >= a1 and
(a > a1 or b>= b1) and
(a > a1 or b > b1 or c > c1)
order by a,b,c limit 1 ^ no offset necessary
confused yet? There is a boolean reverse of the above which is even
more complicated. The above query is correct but the planner doesn't
'get it' beyond the a >= a1 expreesion...not to mention the mental
jumping jacks to get there in the first place. The planner always
'gets' the row constructor expression with Tom's new changes which is
where the performance speedup comes in.
IMO, the sql 92 row constructor was inserted for ISAM style key based
table browsing without cursors...more or less a 'lost art' these days
but still relevant. This is a key strategy in dealing with large
tables. Blog entry is forthcoming :).
Merlin
On Feb 8, 2006, at 11:17 , Merlin Moncure wrote:
The proper SQL construct without row constructor is:
select * from t where
a >= a1 and
(a > a1 or b>= b1) and
(a > a1 or b > b1 or c > c1)
order by a,b,c limit 1 ^ no offset necessaryconfused yet?
This is interesting! Could you also provide the equivalent *with* a
row constructor? (or did I miss that somewhere?)
Michael Glaesemann
grzm myrealbox com
On Feb 8, 2006, at 11:17 , Merlin Moncure wrote:
The proper SQL construct without row constructor is:
select * from t where
a >= a1 and
(a > a1 or b>= b1) and
(a > a1 or b > b1 or c > c1)
order by a,b,c limit 1 ^ no offset necessaryconfused yet?
This is interesting! Could you also provide the equivalent *with* a
row constructor? (or did I miss that somewhere?)
select * from t where (a,b,c) > (a1, b1, c1) order by a,b,c limit 1;
[plus full usage of key on t(a,b,c)]
On Feb 8, 2006, at 11:35 , Merlin Moncure wrote:
On Feb 8, 2006, at 11:17 , Merlin Moncure wrote:
The proper SQL construct without row constructor is:
select * from t where
a >= a1 and
(a > a1 or b>= b1) and
(a > a1 or b > b1 or c > c1)
order by a,b,c limit 1 ^ no offset necessaryconfused yet?
This is interesting! Could you also provide the equivalent *with* a
row constructor? (or did I miss that somewhere?)select * from t where (a,b,c) > (a1, b1, c1) order by a,b,c limit 1;
[plus full usage of key on t(a,b,c)]
Thanks! I'll need to ruminate on this for a while.
Michael Glaesemann
grzm myrealbox com
On Feb 8, 2006, at 11:17 , Merlin Moncure wrote:
The proper SQL construct without row constructor is:
select * from t where
a >= a1 and
(a > a1 or b>= b1) and
(a > a1 or b > b1 or c > c1)
order by a,b,c limit 1 ^ no offset necessaryconfused yet?
No, not confused, that's closer to an example...
This is interesting! Could you also provide the equivalent *with* a
row constructor? (or did I miss that somewhere?)select * from t where (a,b,c) > (a1, b1, c1) order by a,b,c limit 1;
[plus full usage of key on t(a,b,c)]
Ah, so _that_ is the new notation that works now? That's neat indeed.
--
"cbbrowne","@","gmail.com"
http://linuxdatabases.info/info/slony.html
"Instant coffee is like pouring hot water over the cremated remains of
a good friend."
Merlin Moncure <mmoncure@gmail.com> writes:
IMO, the sql 92 row constructor was inserted for ISAM style key based
table browsing without cursors...more or less a 'lost art' these days
but still relevant. This is a key strategy in dealing with large
tables. Blog entry is forthcoming :).
Just for the record, it's not the row constructor stuff that just got
fixed, it's row-value comparison. We were able to construct rows
correctly before, but we didn't compare them in the correct column-
by-column fashion. Please call it by the right name in your blog to
avoid future confusion.
regards, tom lane
Well, I've tested it a bit:
db=# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
(Debian 1:3.3.5-13)
(1 row)
db=# select (1,3) > (2,3);
?column?
----------
f
(1 row)
db=# select (3,3) > (2,3);
?column?
----------
f
(1 row)
db=# select (3,4) > (2,3);
?column?
----------
t
(1 row)
It seems to me that (x,y) > (a,b) means (x > a AND y > b) ... which is
not exactly what you wanted... or I'm missing something ?
Cheers,
Csaba.
Show quoted text
On Wed, 2006-02-08 at 03:35, Merlin Moncure wrote:
On Feb 8, 2006, at 11:17 , Merlin Moncure wrote:
The proper SQL construct without row constructor is:
select * from t where
a >= a1 and
(a > a1 or b>= b1) and
(a > a1 or b > b1 or c > c1)
order by a,b,c limit 1 ^ no offset necessaryconfused yet?
This is interesting! Could you also provide the equivalent *with* a
row constructor? (or did I miss that somewhere?)select * from t where (a,b,c) > (a1, b1, c1) order by a,b,c limit 1;
[plus full usage of key on t(a,b,c)]---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
On Wed, Feb 08, 2006 at 10:38:59AM +0100, Csaba Nagy wrote:
Well, I've tested it a bit:
db=# select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
(Debian 1:3.3.5-13)
(1 row)
It's in CVS HEAD, not 8.1
It seems to me that (x,y) > (a,b) means (x > a AND y > b) ... which is
not exactly what you wanted... or I'm missing something ?
Yes, it changed because someone pointed out that the behaviour in 8.1
was wrong.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
* Martijn van Oosterhout (kleptog@svana.org) wrote:
It's in CVS HEAD, not 8.1
It seems to me that (x,y) > (a,b) means (x > a AND y > b) ... which is
not exactly what you wanted... or I'm missing something ?Yes, it changed because someone pointed out that the behaviour in 8.1
was wrong.
Sounds like a bug, will it be in 8.1.3 or do we have to wait till 8.2
for it? Sounds very interesting indeed...
Thanks!
Stephen
On Wed, Feb 08, 2006 at 07:49:32AM -0500, Stephen Frost wrote:
* Martijn van Oosterhout (kleptog@svana.org) wrote:
It's in CVS HEAD, not 8.1
It seems to me that (x,y) > (a,b) means (x > a AND y > b) ... which is
not exactly what you wanted... or I'm missing something ?Yes, it changed because someone pointed out that the behaviour in 8.1
was wrong.Sounds like a bug, will it be in 8.1.3 or do we have to wait till 8.2
for it? Sounds very interesting indeed...
Well, the bug is really that we accept the syntax but do the wrong
thing. I don't know when it was added but the quick fix would be to
refuse the syntax. I think the changes to make it work were too large
to be in a point release.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
* Martijn van Oosterhout (kleptog@svana.org) wrote:
On Wed, Feb 08, 2006 at 07:49:32AM -0500, Stephen Frost wrote:
Sounds like a bug, will it be in 8.1.3 or do we have to wait till 8.2
for it? Sounds very interesting indeed...Well, the bug is really that we accept the syntax but do the wrong
thing. I don't know when it was added but the quick fix would be to
refuse the syntax. I think the changes to make it work were too large
to be in a point release.
Ah, ok, I misunderstood. Looking forwrad to having it (and having it
work correctly!) in 8.2 :)
Thanks,
Stephen
On 2/8/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Just for the record, it's not the row constructor stuff that just got
fixed, it's row-value comparison. We were able to construct rows
correctly before, but we didn't compare them in the correct column-
by-column fashion. Please call it by the right name in your blog to
avoid future confusion.
right..that was poor phrasing on my part. will fix....
merlin