Thread: 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.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? 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 necessary > > confused 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 necessary > > > > confused 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 necessary >>> >>> confused 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 necessary >> > >> > confused 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. 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 necessary > > > > > > confused 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/ > 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/ > 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