Caution: tonight's commits force initdb

Hi

-----Original Message-----
From: owner-pgsql-hackers@postgreSQL.org
[mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
Sent: Saturday, August 21, 1999 12:58 PM
To: pgsql-hackers@postgreSQL.org
Subject: [HACKERS] Caution: tonight's commits force initdb

I have just committed changes that alter the representation of
SortClause nodes (making them like GroupClause, instead of the
crufty way they were done before). This breaks stored rules!
You will need to initdb next time you pull current sources...

The good news is that the optimizer is finally reasonably smart
about avoiding a top-level Sort operation.

Thanks for your good jobs.

After applying this change,I tested some cases.
For a table t,explain shows

explain select * from t;

NOTICE: QUERY PLAN:

Seq Scan on t (cost=1716.32 rows=27131 width=612)

And with ORDER BY clause

explain select * from t order by key;

NOTICE: QUERY PLAN:

Index Scan using t_pkey on t (cost=2284.55 rows=27131 width=612)

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

I have felt that the current cost estimation of index scan is too small,
though I have no alternative.

Comments ?

Hiroshi Inoue
Inoue@tpf.co.jp

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 and
cost 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