Thread: sql row constructor...works!

sql row constructor...works!

From
Merlin Moncure
Date:
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


Re: sql row constructor...works!

From
Josh Berkus
Date:
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


Re: sql row constructor...works!

From
Tom Lane
Date:
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


Re: sql row constructor...works!

From
Tom Lane
Date:
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


Re: sql row constructor...works!

From
Christopher Browne
Date:
> 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?'"


Re: sql row constructor...works!

From
Merlin Moncure
Date:
> > 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


Re: sql row constructor...works!

From
Michael Glaesemann
Date:
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





Re: sql row constructor...works!

From
Merlin Moncure
Date:
> 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)]


Re: sql row constructor...works!

From
Michael Glaesemann
Date:
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





Re: sql row constructor...works!

From
Christopher Browne
Date:
>> 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."


Re: sql row constructor...works!

From
Tom Lane
Date:
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


Re: sql row constructor...works!

From
Csaba Nagy
Date:
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



Re: sql row constructor...works!

From
Martijn van Oosterhout
Date:
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.

Re: sql row constructor...works!

From
Stephen Frost
Date:
* 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

Re: sql row constructor...works!

From
Martijn van Oosterhout
Date:
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.

Re: sql row constructor...works!

From
Stephen Frost
Date:
* 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

Re: sql row constructor...works!

From
Merlin Moncure
Date:
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