Hi all

In looking for how to do table constraints psql help says:

software=> \h create table
Command: create table
Description: create a new table
Syntax:
        CREATE TABLE class_name
        (attr1 type1 [DEFAULT expression] [NOT NULL], ...attrN)
        [INHERITS (class_name1, ...class_nameN)
        [[CONSTRAINT name] CHECK condition1, ...conditionN] ]
;


But this both does not work, and does not agree with "The Practical SQL
Handbook", the examples of which do work.

Should the syntax not be more like: (constraint inside the main parens)

Command: create table
Description: create a new table
Syntax:
        CREATE TABLE class_name
        (attr1 type1 [DEFAULT expression] [NOT NULL][, ...attrN]
        [,[CONSTRAINT name] CHECK condition1, ...conditionN] ]);

I'm not sure where to put:
        [INHERITS (class_name1, ...class_nameN)
as I've never used it. But I suspect it may need inside the '()' as well,
no?

OH, also, what is / is there, a comment character to use in SQL scripts
feed into psql?

Have a great day
Terry Mackintosh <terry@terrym.com>               http://www.terrym.com
sysadmin/owner                  I'm excited about life! How about YOU!?

Proudly powered by R H Linux 4.2, Apache 1.3.x, PHP 3.x, PostgreSQL 6.x
-----------------------------------------------------------------------
Only if you know where you're going can you get there.


Mon, 25 Jan 1999 15:19:06 -0500 (EST)
Terry Mackintosh <terry@terrym.com>
help bug and comment char.
> In looking for how to do table constraints psql help says:
> software=> \h create table
> Command: create table
> Description: create a new table
> Syntax:
>         CREATE TABLE class_name
>         (attr1 type1 [DEFAULT expression] [NOT NULL], ...attrN)
>         [INHERITS (class_name1, ...class_nameN)
>         [[CONSTRAINT name] CHECK condition1, ...conditionN]]

This syntax help is out of date. The syntax for v6.4 (and perhaps
v6.3.2) became compatible with SQL92, except of course for the INHERITS
clause. That still must appear outside of the column-definition parens.

                    - Tom

Tue, 26 Jan 1999 03:19:25 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] help bug and comment char.
> Hi all
> 
> In looking for how to do table constraints psql help says:
> 
> software=> \h create table
> Command: create table
> Description: create a new table
> Syntax:
>         CREATE TABLE class_name
>         (attr1 type1 [DEFAULT expression] [NOT NULL], ...attrN)
>         [INHERITS (class_name1, ...class_nameN)
>         [[CONSTRAINT name] CHECK condition1, ...conditionN] ]
> ;
> 
> 
> But this both does not work, and does not agree with "The Practical SQL
> Handbook", the examples of which do work.
> 
> Should the syntax not be more like: (constraint inside the main parens)
> 
> Command: create table
> Description: create a new table
> Syntax:
>         CREATE TABLE class_name
>         (attr1 type1 [DEFAULT expression] [NOT NULL][, ...attrN]
>         [,[CONSTRAINT name] CHECK condition1, ...conditionN] ]);

Fixed.


> 
> I'm not sure where to put:
>         [INHERITS (class_name1, ...class_nameN)
> as I've never used it. But I suspect it may need inside the '()' as well,
> no?
> 
> OH, also, what is / is there, a comment character to use in SQL scripts
> feed into psql?

-- is the comment character.  Man sql says:

---------------------------------------------------------------------------

Comments
       A comment is an arbitrary sequence of characters following
       double dashes up to the end of the line.  We also  support
       double-slashes as comments, e.g.:
       -- This is a standard SQL comment
       // And this is another supported comment style, like C++

       We also support C-style comments, e.g.:
       /* multi
          line
          comment */



-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 2 Feb 1999 13:40:08 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] help bug and comment char.
> > In looking for how to do table constraints psql help says:
> > software=> \h create table
> > Command: create table
> > Description: create a new table
> > Syntax:
> >         CREATE TABLE class_name
> >         (attr1 type1 [DEFAULT expression] [NOT NULL], ...attrN)
> >         [INHERITS (class_name1, ...class_nameN)
> >         [[CONSTRAINT name] CHECK condition1, ...conditionN]]
> 
> This syntax help is out of date. The syntax for v6.4 (and perhaps
> v6.3.2) became compatible with SQL92, except of course for the INHERITS
> clause. That still must appear outside of the column-definition parens.

Fixed.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 2 Feb 1999 13:42:10 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] help bug and comment char.
Hi PostgreSQL hackers

As we are again approaching the beta (feature freeze), 
I will ask my ordinary question ;)

Is the patch by Jan that eliminated the duplicate sort node in case it
was redundant included in 6.5 ?

---------------
Hannu

Tue, 02 Feb 1999 21:22:13 +0200
Hannu Krosing <hannu@trust.ee>
6.5 beta and ORDER BY patch
>
> Hi PostgreSQL hackers
>
> As we are again approaching the beta (feature freeze),
> I will ask my ordinary question ;)
>
> Is the patch by Jan that eliminated the duplicate sort node in case it
> was redundant included in 6.5 ?

    Sorry,

    I  missed  to  put  it  into after v6.4 release. And since it
    wasn't there during v6.5 development, I would not put  it  in
    now.

    Note that it wasn't in the v6.4 feature patches either, so it
    isn't tested enough to get released.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Tue, 2 Feb 1999 23:58:44 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 beta and ORDER BY patch
> >
> > Hi PostgreSQL hackers
> >
> > As we are again approaching the beta (feature freeze),
> > I will ask my ordinary question ;)
> >
> > Is the patch by Jan that eliminated the duplicate sort node in case it
> > was redundant included in 6.5 ?
> 
>     Sorry,
> 
>     I  missed  to  put  it  into after v6.4 release. And since it
>     wasn't there during v6.5 development, I would not put  it  in
>     now.
> 
>     Note that it wasn't in the v6.4 feature patches either, so it
>     isn't tested enough to get released.

We haven't started beta yet.  Anything on LIMIT?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 2 Feb 1999 18:07:13 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 beta and ORDER BY patch
>
> > >
> > > Hi PostgreSQL hackers
> > >
> > > As we are again approaching the beta (feature freeze),
> > > I will ask my ordinary question ;)
> > >
> > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > was redundant included in 6.5 ?
> >
> >     Sorry,
> >
> >     I  missed  to  put  it  into after v6.4 release. And since it
> >     wasn't there during v6.5 development, I would not put  it  in
> >     now.
> >
> >     Note that it wasn't in the v6.4 feature patches either, so it
> >     isn't tested enough to get released.
>
> We haven't started beta yet.  Anything on LIMIT?

    LIMIT  is  in  there  and was during entire v6.5 development.
    But ORDER BY suppressing sort using index wasn't.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 3 Feb 1999 00:16:12 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 beta and ORDER BY patch
> > We haven't started beta yet.  Anything on LIMIT?
> 
>     LIMIT  is  in  there  and was during entire v6.5 development.
>     But ORDER BY suppressing sort using index wasn't.
> 

Great.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 2 Feb 1999 18:16:24 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 beta and ORDER BY patch
Jan Wieck wrote:
> 
> >
> > Hi PostgreSQL hackers
> >
> > As we are again approaching the beta (feature freeze),
> > I will ask my ordinary question ;)
> >
> > Is the patch by Jan that eliminated the duplicate sort node in case it
> > was redundant included in 6.5 ?
> 
>     Sorry,
> 
>     I  missed  to  put  it  into after v6.4 release. And since it
>     wasn't there during v6.5 development, I would not put  it  in
>     now.
> 
>     Note that it wasn't in the v6.4 feature patches either, so it
>     isn't tested enough to get released.

But if it is not relesed it will _never_ be tested enough ...

As we are just going into beta, not relese, I would suggest to put 
it in now, and back out if it relly breaks anything. 

I have been using it with 6.4 almost since the relese an have 
seen no problems - in fact it solved a big problem and provided about 
1000X speedup for certain queries (a fraction of second instead of 
6 minutes) , not to mention avoiding backend crashes due to disk space 
exhaustion.

And it did not break anything in regression tests either, the only 
argument then was that there is nothing in regression tests that 
could possibly be broken by it ;)

I greatly prefer it over my previous method of doing the same on the 
client side (issuing an EXPLAIN, parsing it to see if it is SORT on 
INDEX SCAN, and omitting the ORDER BY if it is)

Also, not having it greatly diminishes the value of LIMIT.

I agree that it is a hack and only a partial solution and that in 
ideal world the optimiser would also know about sort nodes. 

But it is a very useful hack, and for some (like me) it is 
much bigger improvement than some 10% due to better memory 
allocation (which is of course great too).


----------------
Hannu

Wed, 03 Feb 1999 12:17:10 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] 6.5 beta and ORDER BY patch
Hannu Krosing wrote:
> 
> Jan Wieck wrote:
> > >
> > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > was redundant included in 6.5 ?
> >
> >     Sorry,
> >
> >     I  missed  to  put  it  into after v6.4 release. And since it
> >     wasn't there during v6.5 development, I would not put  it  in

...

> But if it is not relesed it will _never_ be tested enough ...
> 
> As we are just going into beta, not relese, I would suggest to put
> it in now, and back out if it relly breaks anything.

I will download the latest snapshot tonight and test the patch there.

Does anyone know if something introduced in 6.5 can break by omitting 
the top sort node ? 

Perhaps any of the following:

 * MVCC 

 * temp tables 

 * Some exotic use of rules 

 * SELECT FOR UPDATE

I myself can't see how it could break, as the only thing the patch does
is omitting a top sort node if the query is already in the right 
order. So it should be equivalent of just not including the ORDER BY
in the SELECT in the first place.

Jan - I often feel the same about some of my code that are part of some
larger complex project (ie. if it aint broke, don't fix it), but this
time
I think the patch is quite safe, and very very useful for at least two 
occasions: getting the start of some table out to users web and for
processing
huge tables in predictable/repeatable order.

I somewhat understand your hesitation, because I can't either think of
any test
in regression that could be broken by the patch, but instead of making
me 
uneasy it makes me happy ;)

-----------------
Hannu

Wed, 03 Feb 1999 12:38:53 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] 6.5 beta and ORDER BY patch
Hannu Krosing wrote:

>
> Jan Wieck wrote:
> >
> > >
> > > Hi PostgreSQL hackers
> > >
> > > As we are again approaching the beta (feature freeze),
> > > I will ask my ordinary question ;)
> > >
> > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > was redundant included in 6.5 ?
> >
> >     Sorry,
> >
> >     I  missed  to  put  it  into after v6.4 release. And since it
> >     wasn't there during v6.5 development, I would not put  it  in
> >     now.
> >
> >     Note that it wasn't in the v6.4 feature patches either, so it
> >     isn't tested enough to get released.
>
> But if it is not relesed it will _never_ be tested enough ...
>
> As we are just going into beta, not relese, I would suggest to put
> it in now, and back out if it relly breaks anything.
>
> I have been using it with 6.4 almost since the relese an have
> seen no problems - in fact it solved a big problem and provided about
> 1000X speedup for certain queries (a fraction of second instead of
> 6 minutes) , not to mention avoiding backend crashes due to disk space
> exhaustion.
>
> And it did not break anything in regression tests either, the only
> argument then was that there is nothing in regression tests that
> could possibly be broken by it ;)
>
> I greatly prefer it over my previous method of doing the same on the
> client side (issuing an EXPLAIN, parsing it to see if it is SORT on
> INDEX SCAN, and omitting the ORDER BY if it is)
>
> Also, not having it greatly diminishes the value of LIMIT.

    Ok ok ok - OK. You got me, I'll go ahead and put it in.

>
> I agree that it is a hack and only a partial solution and that in
> ideal world the optimiser would also know about sort nodes.

    First  the  executor  must  know better how to handle LIMIT's
    OFFSET.  For now it  processes  the  query  until  OFFSET  is
    reached,  simply  suppressing  the  in  fact  produced result
    tuples in the output. The it stops sending if the LIMIT count
    is  reached.   For  joins  or other complex things, it has no
    chance to do something different. But for an  indexed  single
    table  scan,  where  ALL  the  qualifications are done on the
    index, it should handle the OFFSET by skipping  index  tuples
    only.

    Second  the  optimizer  must  take  LIMIT  into  account  and
    depending on the known number of  tuples,  LIMIT  and  OFFSET
    produce  an  index  scan even if the query isn't qualified at
    all but has an ORDER BY clause matched by the index.

    These two  features  would  finally  solve  your  huge  table
    problems.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 3 Feb 1999 12:07:14 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 beta and ORDER BY patch
On Wed, 3 Feb 1999, Jan Wieck wrote:

> >
> > > >
> > > > Hi PostgreSQL hackers
> > > >
> > > > As we are again approaching the beta (feature freeze),
> > > > I will ask my ordinary question ;)
> > > >
> > > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > > was redundant included in 6.5 ?
> > >
> > >     Sorry,
> > >
> > >     I  missed  to  put  it  into after v6.4 release. And since it
> > >     wasn't there during v6.5 development, I would not put  it  in
> > >     now.
> > >
> > >     Note that it wasn't in the v6.4 feature patches either, so it
> > >     isn't tested enough to get released.
> >
> > We haven't started beta yet.  Anything on LIMIT?
> 
>     LIMIT  is  in  there  and was during entire v6.5 development.
>     But ORDER BY suppressing sort using index wasn't.

Sinc we haven't started BETA yet, why not throw it in?  Once beta, we stil
lhave another month of testing before release, so lots of time...

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 3 Feb 1999 11:38:19 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] 6.5 beta and ORDER BY patch
Jan Wieck wrote:
> 
> 
>     Ok ok ok - OK. You got me, I'll go ahead and put it in.

Thanks ;)
 
> > I agree that it is a hack and only a partial solution and that in
> > ideal world the optimiser would also know about sort nodes.
> 
>     First  the  executor  must  know better how to handle LIMIT's
>     OFFSET.  For now it  processes  the  query  until  OFFSET  is
>     reached,  simply  suppressing  the  in  fact  produced result
>     tuples in the output. The it stops sending if the LIMIT count
>     is  reached.   For  joins  or other complex things, it has no
>     chance to do something different. But for an  indexed  single
>     table  scan,  where  ALL  the  qualifications are done on the
>     index, it should handle the OFFSET by skipping  index  tuples
>     only.

And we must also tie this kind of scan to triggers (my quess is that 
currently the triggers are fired by accessing the data in the actual
relation data).

It probably does not affect rules as much, though it would be cool to 
define rules for index scans or sort nodes.

>     Second  the  optimizer  must  take  LIMIT  into  account  and
>     depending on the known number of  tuples,  LIMIT  and  OFFSET
>     produce  an  index  scan even if the query isn't qualified at
>     all but has an ORDER BY clause matched by the index.
> 
>     These two  features  would  finally  solve  your  huge  table
>     problems.

Yes, it seems so.

Next thing to attack then would be aggregates, so that they too can 
benefit from indexes, I can immediately think of MIN, MAX and COUNT
on simple scans. But as the aggregates are user-defined, we probably 
need a flag that tells the optimiser if said aggregate can in fact 
use indexes (and what type of index)

Maybe we can even cache some data (for example tuple count) in 
backend, so that COUNT(*) can be made real fast ?

After that the reverse index scans, so that the index that are 
backwards can also be used for sorting.
BTW, can this be easily implemented/effective in PostgreSQL or are
our btree indexes optimised for forward scans ?

Also, how do indexes interact with TRX manager (is there some docs
on it).

---------------------
Hannu

Wed, 03 Feb 1999 20:42:43 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] 6.5 beta and ORDER BY patch
> Next thing to attack then would be aggregates, so that they too can 
> benefit from indexes, I can immediately think of MIN, MAX and COUNT
> on simple scans. But as the aggregates are user-defined, we probably 
> need a flag that tells the optimiser if said aggregate can in fact 
> use indexes (and what type of index)
> 
> Maybe we can even cache some data (for example tuple count) in 
> backend, so that COUNT(*) can be made real fast ?
> 
> After that the reverse index scans, so that the index that are 
> backwards can also be used for sorting.
> BTW, can this be easily implemented/effective in PostgreSQL or are
> our btree indexes optimised for forward scans ?

Jan, I have kept the postings on optimizing LIMIT for joins.  Let me
know if/when you want to see them.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 3 Feb 1999 13:46:22 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 beta and ORDER BY patch
> Jan, I have kept the postings on optimizing LIMIT for joins.  Let me
> know if/when you want to see them.

    Are  they  patches  ready to go in or just suggestions how to
    do?

    ORDER BY patch is now in CURRENT.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 3 Feb 1999 20:37:17 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 beta and ORDER BY patch
Hello all,

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Hannu Krosing
> Sent: Thursday, February 04, 1999 3:43 AM
> To: Jan Wieck
> Cc: hackers@postgreSQL.org
> Subject: Re: [HACKERS] 6.5 beta and ORDER BY patch
>

[snip]
 
> 
> After that the reverse index scans, so that the index that are 
> backwards can also be used for sorting.
> BTW, can this be easily implemented/effective in PostgreSQL or are
> our btree indexes optimised for forward scans ?
>

PostgreSQL seems to have the ability to scan Index backward 
because we can execute "fetch backward" command. 
IMHO _bt_first() fucntion used to find first item in a scan should 
be changed to work well in case of backward positioning.

I think this change also gives the partial solution for the problem 
[ [HACKERS] Cursor Movement - Past the End ] reported by 
David Hartwig. 

I have a sample code for this change.
I can send it if someone want to check or test it.
 
Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp

Thu, 4 Feb 1999 12:48:24 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] 6.5 beta and ORDER BY patch

Jan, is this implemented in 6.5 beta?

> > > > As we are again approaching the beta (feature freeze),
> > > > I will ask my ordinary question ;)
> > > >
> > > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > > was redundant included in 6.5 ?
> > >
> > >     Sorry,
> > >
> > >     I  missed  to  put  it  into after v6.4 release. And since it
> > >     wasn't there during v6.5 development, I would not put  it  in
> > >     now.
> > >
> > >     Note that it wasn't in the v6.4 feature patches either, so it
> > >     isn't tested enough to get released.
> >
> > But if it is not relesed it will _never_ be tested enough ...
> >
> > As we are just going into beta, not relese, I would suggest to put
> > it in now, and back out if it relly breaks anything.
> >
> > I have been using it with 6.4 almost since the relese an have
> > seen no problems - in fact it solved a big problem and provided about
> > 1000X speedup for certain queries (a fraction of second instead of
> > 6 minutes) , not to mention avoiding backend crashes due to disk space
> > exhaustion.
> >
> > And it did not break anything in regression tests either, the only
> > argument then was that there is nothing in regression tests that
> > could possibly be broken by it ;)
> >
> > I greatly prefer it over my previous method of doing the same on the
> > client side (issuing an EXPLAIN, parsing it to see if it is SORT on
> > INDEX SCAN, and omitting the ORDER BY if it is)
> >
> > Also, not having it greatly diminishes the value of LIMIT.
> 
>     Ok ok ok - OK. You got me, I'll go ahead and put it in.
> 
> >
> > I agree that it is a hack and only a partial solution and that in
> > ideal world the optimiser would also know about sort nodes.
> 
>     First  the  executor  must  know better how to handle LIMIT's
>     OFFSET.  For now it  processes  the  query  until  OFFSET  is
>     reached,  simply  suppressing  the  in  fact  produced result
>     tuples in the output. The it stops sending if the LIMIT count
>     is  reached.   For  joins  or other complex things, it has no
>     chance to do something different. But for an  indexed  single
>     table  scan,  where  ALL  the  qualifications are done on the
>     index, it should handle the OFFSET by skipping  index  tuples
>     only.
> 
>     Second  the  optimizer  must  take  LIMIT  into  account  and
>     depending on the known number of  tuples,  LIMIT  and  OFFSET
>     produce  an  index  scan even if the query isn't qualified at
>     all but has an ORDER BY clause matched by the index.
> 
>     These two  features  would  finally  solve  your  huge  table
>     problems.
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 9 May 1999 07:54:58 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 beta and ORDER BY patch
It is my assumption this has been applied to 6.5 beta, right?

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Hello all,
> 
> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Hannu Krosing
> > Sent: Thursday, February 04, 1999 3:43 AM
> > To: Jan Wieck
> > Cc: hackers@postgreSQL.org
> > Subject: Re: [HACKERS] 6.5 beta and ORDER BY patch
> >
> 
> [snip]
>  
> > 
> > After that the reverse index scans, so that the index that are 
> > backwards can also be used for sorting.
> > BTW, can this be easily implemented/effective in PostgreSQL or are
> > our btree indexes optimised for forward scans ?
> >
> 
> PostgreSQL seems to have the ability to scan Index backward 
> because we can execute "fetch backward" command. 
> IMHO _bt_first() fucntion used to find first item in a scan should 
> be changed to work well in case of backward positioning.
> 
> I think this change also gives the partial solution for the problem 
> [ [HACKERS] Cursor Movement - Past the End ] reported by 
> David Hartwig. 
> 
> I have a sample code for this change.
> I can send it if someone want to check or test it.
>  
> Thanks.
> 
> Hiroshi Inoue
> Inoue@tpf.co.jp
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 9 May 1999 07:55:33 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 beta and ORDER BY patch

> -----Original Message-----
> From: Bruce Momjian [mailto:maillist@candle.pha.pa.us]
> Sent: Sunday, May 09, 1999 8:56 PM
> To: Hiroshi Inoue
> Cc: Hannu Krosing; David Hartwig; Jan Wieck; pgsql-hackers
> Subject: Re: [HACKERS] 6.5 beta and ORDER BY patch
> 
> 
> It is my assumption this has been applied to 6.5 beta, right?
>
 
It has been applied with subject  [Index backward scan patch].
However it doesn't include a change to omit sorting in all descending 
ORDER BY cases.  

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp

> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > Hello all,
> > 
> > > -----Original Message-----
> > > From: owner-pgsql-hackers@postgreSQL.org
> > > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Hannu Krosing
> > > Sent: Thursday, February 04, 1999 3:43 AM
> > > To: Jan Wieck
> > > Cc: hackers@postgreSQL.org
> > > Subject: Re: [HACKERS] 6.5 beta and ORDER BY patch
> > >
> > 
> > [snip]
> >  
> > > 
> > > After that the reverse index scans, so that the index that are 
> > > backwards can also be used for sorting.
> > > BTW, can this be easily implemented/effective in PostgreSQL or are
> > > our btree indexes optimised for forward scans ?
> > >
> > 
> > PostgreSQL seems to have the ability to scan Index backward 
> > because we can execute "fetch backward" command. 
> > IMHO _bt_first() fucntion used to find first item in a scan should 
> > be changed to work well in case of backward positioning.
> > 
> > I think this change also gives the partial solution for the problem 
> > [ [HACKERS] Cursor Movement - Past the End ] reported by 
> > David Hartwig. 
> > 
> > I have a sample code for this change.
> > I can send it if someone want to check or test it.
> >  
> > Thanks.
> > 
> > Hiroshi Inoue
> > Inoue@tpf.co.jp
> > 
> > 
> 
> 
> -- 
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 

Mon, 10 May 1999 10:24:15 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] 6.5 beta and ORDER BY patch
>
>
> Jan, is this implemented in 6.5 beta?

    It is still the simple suppressing of the sort if the choosen
    index scan has already the requested sort order. The possible
    enhancements  of  the  optimizer  (mainly  taking  LIMIT into
    account and use index scan if sort order can be obtained from
    that) aren't implemented AFAIK.

    I  have too less knowledge in the planner/optimizer corner to
    get my hands on it at this stage! And there are  things  left
    in  the  rewrite system. It might be better to leave this all
    for v6.6.


Jan

>
> > > > > As we are again approaching the beta (feature freeze),
> > > > > I will ask my ordinary question ;)
> > > > >
> > > > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > > > was redundant included in 6.5 ?
> > > >
> > > >     Sorry,
> > > >
> > > >     I  missed  to  put  it  into after v6.4 release. And since it
> > > >     wasn't there during v6.5 development, I would not put  it  in
> > > >     now.
> > > >
> > > >     Note that it wasn't in the v6.4 feature patches either, so it
> > > >     isn't tested enough to get released.
> > >
> > > But if it is not relesed it will _never_ be tested enough ...
> > >
> > > As we are just going into beta, not relese, I would suggest to put
> > > it in now, and back out if it relly breaks anything.
> > >
> > > I have been using it with 6.4 almost since the relese an have
> > > seen no problems - in fact it solved a big problem and provided about
> > > 1000X speedup for certain queries (a fraction of second instead of
> > > 6 minutes) , not to mention avoiding backend crashes due to disk space
> > > exhaustion.
> > >
> > > And it did not break anything in regression tests either, the only
> > > argument then was that there is nothing in regression tests that
> > > could possibly be broken by it ;)
> > >
> > > I greatly prefer it over my previous method of doing the same on the
> > > client side (issuing an EXPLAIN, parsing it to see if it is SORT on
> > > INDEX SCAN, and omitting the ORDER BY if it is)
> > >
> > > Also, not having it greatly diminishes the value of LIMIT.
> >
> >     Ok ok ok - OK. You got me, I'll go ahead and put it in.
> >
> > >
> > > I agree that it is a hack and only a partial solution and that in
> > > ideal world the optimiser would also know about sort nodes.
> >
> >     First  the  executor  must  know better how to handle LIMIT's
> >     OFFSET.  For now it  processes  the  query  until  OFFSET  is
> >     reached,  simply  suppressing  the  in  fact  produced result
> >     tuples in the output. The it stops sending if the LIMIT count
> >     is  reached.   For  joins  or other complex things, it has no
> >     chance to do something different. But for an  indexed  single
> >     table  scan,  where  ALL  the  qualifications are done on the
> >     index, it should handle the OFFSET by skipping  index  tuples
> >     only.
> >
> >     Second  the  optimizer  must  take  LIMIT  into  account  and
> >     depending on the known number of  tuples,  LIMIT  and  OFFSET
> >     produce  an  index  scan even if the query isn't qualified at
> >     all but has an ORDER BY clause matched by the index.
> >
> >     These two  features  would  finally  solve  your  huge  table
> >     problems.
> >
>
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 10 May 1999 16:47:26 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 beta and ORDER BY patch
>
> It is my assumption this has been applied to 6.5 beta, right?

    Don't know. Hiroshi - do you see your code anywhere?


Jan

>
> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > Hello all,
> >
> > > -----Original Message-----
> > > From: owner-pgsql-hackers@postgreSQL.org
> > > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Hannu Krosing
> > > Sent: Thursday, February 04, 1999 3:43 AM
> > > To: Jan Wieck
> > > Cc: hackers@postgreSQL.org
> > > Subject: Re: [HACKERS] 6.5 beta and ORDER BY patch
> > >
> >
> > [snip]
> >
> > >
> > > After that the reverse index scans, so that the index that are
> > > backwards can also be used for sorting.
> > > BTW, can this be easily implemented/effective in PostgreSQL or are
> > > our btree indexes optimised for forward scans ?
> > >
> >
> > PostgreSQL seems to have the ability to scan Index backward
> > because we can execute "fetch backward" command.
> > IMHO _bt_first() fucntion used to find first item in a scan should
> > be changed to work well in case of backward positioning.
> >
> > I think this change also gives the partial solution for the problem
> > [ [HACKERS] Cursor Movement - Past the End ] reported by
> > David Hartwig.
> >
> > I have a sample code for this change.
> > I can send it if someone want to check or test it.
> >
> > Thanks.
> >
> > Hiroshi Inoue
> > Inoue@tpf.co.jp
> >
> >
>
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 10 May 1999 16:49:11 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] 6.5 beta and ORDER BY patch


Added to TODO:

	* Have optimizer take LIMIT into account when considering index scans

> Hannu Krosing wrote:
> 
> >
> > Jan Wieck wrote:
> > >
> > > >
> > > > Hi PostgreSQL hackers
> > > >
> > > > As we are again approaching the beta (feature freeze),
> > > > I will ask my ordinary question ;)
> > > >
> > > > Is the patch by Jan that eliminated the duplicate sort node in case it
> > > > was redundant included in 6.5 ?
> > >
> > >     Sorry,
> > >
> > >     I  missed  to  put  it  into after v6.4 release. And since it
> > >     wasn't there during v6.5 development, I would not put  it  in
> > >     now.
> > >
> > >     Note that it wasn't in the v6.4 feature patches either, so it
> > >     isn't tested enough to get released.
> >
> > But if it is not relesed it will _never_ be tested enough ...
> >
> > As we are just going into beta, not relese, I would suggest to put
> > it in now, and back out if it relly breaks anything.
> >
> > I have been using it with 6.4 almost since the relese an have
> > seen no problems - in fact it solved a big problem and provided about
> > 1000X speedup for certain queries (a fraction of second instead of
> > 6 minutes) , not to mention avoiding backend crashes due to disk space
> > exhaustion.
> >
> > And it did not break anything in regression tests either, the only
> > argument then was that there is nothing in regression tests that
> > could possibly be broken by it ;)
> >
> > I greatly prefer it over my previous method of doing the same on the
> > client side (issuing an EXPLAIN, parsing it to see if it is SORT on
> > INDEX SCAN, and omitting the ORDER BY if it is)
> >
> > Also, not having it greatly diminishes the value of LIMIT.
> 
>     Ok ok ok - OK. You got me, I'll go ahead and put it in.
> 
> >
> > I agree that it is a hack and only a partial solution and that in
> > ideal world the optimiser would also know about sort nodes.
> 
>     First  the  executor  must  know better how to handle LIMIT's
>     OFFSET.  For now it  processes  the  query  until  OFFSET  is
>     reached,  simply  suppressing  the  in  fact  produced result
>     tuples in the output. The it stops sending if the LIMIT count
>     is  reached.   For  joins  or other complex things, it has no
>     chance to do something different. But for an  indexed  single
>     table  scan,  where  ALL  the  qualifications are done on the
>     index, it should handle the OFFSET by skipping  index  tuples
>     only.
> 
>     Second  the  optimizer  must  take  LIMIT  into  account  and
>     depending on the known number of  tuples,  LIMIT  and  OFFSET
>     produce  an  index  scan even if the query isn't qualified at
>     all but has an ORDER BY clause matched by the index.
> 
>     These two  features  would  finally  solve  your  huge  table
>     problems.
> 
> 
> Jan
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #======================================== jwieck@debis.com (Jan Wieck) #
> 
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 6 Jul 1999 21:49:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 6.5 beta and ORDER BY patch