Thread: AW: [HACKERS] Caution: tonight's commits force initdb

> Hmm,Index scan is chosen to select all rows.
> AFAIK,sequential scan + sort is much faster than index scan in
> most cases.
> 
>     cost of index scan < cost of sequential scan + cost of sort
> 
This is usually true. It might need resources though that are not available,
e.g. 8 GB sort space. It also depends on whether the application is
interested in
first row (interactive), or all row performance (batch). Other DB's can
switch modes 
to decide on the wanted behavior. So I think there is no yes/no decision on
this.

Andreas

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Zeugswetter
> Andreas IZ5
> Sent: Tuesday, August 24, 1999 6:48 PM
> To: pgsql-hackers
> Subject: AW: [HACKERS] Caution: tonight's commits force initdb
> 
> 
> 
> > Hmm,Index scan is chosen to select all rows.
> > AFAIK,sequential scan + sort is much faster than index scan in
> > most cases.
> > 
> >     cost of index scan < cost of sequential scan + cost of sort
> > 
> This is usually true. It might need resources though that are not 
> available,

Without taking SORT into account
[From my example]
cost of sequential scan = 1716.32 andcost of index scan = 2284.55
cost of sequential scan > cost of index scan * 0.7

It's unbelievable for me.

> e.g. 8 GB sort space. It also depends on whether the application is
> interested in
> first row (interactive), or all row performance (batch). Other DB's can
> switch modes 
> to decide on the wanted behavior. So I think there is no yes/no 
> decision on
> this.
>

We could use LIMIT clause to get first rows now and optimizer
should take LIMIT/OFFSET into account(TODO item).

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Zeugswetter Andreas IZ5 wrote:
> 
> > Hmm,Index scan is chosen to select all rows.
> > AFAIK,sequential scan + sort is much faster than index scan in
> > most cases.
> >
> >       cost of index scan < cost of sequential scan + cost of sort
> >
> This is usually true. It might need resources though that are not available,
> e.g. 8 GB sort space. It also depends on whether the application is
> interested in
> first row (interactive), or all row performance (batch). Other DB's can
> switch modes
> to decide on the wanted behavior. So I think there is no yes/no decision on
> this.

I feel the decision should be based on all resources required including
CPU, Memory, and I/O by both the server and all clients. In my experience the
index scan *always* comes out on top on average for small, medium and large
result sets with single row fetch. Now if only we can get postgres to support 
single row fetch without having to use transactions and cursors... then I 
believe that postgres could give Informix and Oracle a serious run for 
their money.

--------
Regards
Theo