Re: Re: Performance degradation in PostgreSQL 7.1beta3 vs

Dear Hannu,

"Robert E. Bruccoleri" wrote:

Dear Hannu,

"Robert E. Bruccoleri" wrote:

explain select count(*) from comparisons_4 where code = 80003;
NOTICE: QUERY PLAN:

Aggregate (cost=15659.29..15659.29 rows=1 width=0)
-> Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=7391 width=0)

EXPLAIN

What is the type of field "code" ?

int4

Do you think that should make a difference?

Probably not here.

Sometimes it has made difference if the system does not recognize
the other side of comparison (80003) as being of the same type as
the index.

what are the cost estimates when you run explain with seqscan disabled ?
do => SET ENABLE_SEQSCAN TO OFF;
see:
(http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm#RUNTIME-CONFIG-OPTIMIZER)

Here's the result from EXPLAIN:

Aggregate (cost=19966.21..19966.21 rows=1 width=0)
-> Index Scan using comparisons_4_code on comparisons_4 (cost=0.00..19947.73 rows=7391 width=0)

The estimates are too high.

--Bob

+----------------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D.      | Phone: 609 737 6383                |
| President, Congenomics, Inc.     | Fax:   609 737 7528                |
| 114 W Franklin Ave, Suite K1,4,5 | email: bruc@acm.org                |
| P.O. Box 314                     | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534             |                                    |
+----------------------------------+------------------------------------+

"Robert E. Bruccoleri" wrote:

Dear Hannu,

"Robert E. Bruccoleri" wrote:

explain select count(*) from comparisons_4 where code = 80003;
NOTICE: QUERY PLAN:

Aggregate (cost=15659.29..15659.29 rows=1 width=0)
-> Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=7391 width=0)

EXPLAIN

What is the type of field "code" ?

int4

Do you think that should make a difference?

Probably not here.

Sometimes it has made difference if the system does not recognize
the other side of comparison (80003) as being of the same type as
the index.

what are the cost estimates when you run explain with seqscan disabled ?
do => SET ENABLE_SEQSCAN TO OFF;
see:
(http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm#RUNTIME-CONFIG-OPTIMIZER)
-----------------
Hannu

"Robert E. Bruccoleri" wrote:

what are the cost estimates when you run explain with seqscan disabled ?
do => SET ENABLE_SEQSCAN TO OFF;
see:
(http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm#RUNTIME-CONFIG-OPTIMIZER)

Here's the result from EXPLAIN:

Aggregate (cost=19966.21..19966.21 rows=1 width=0)
-> Index Scan using comparisons_4_code on comparisons_4 (cost=0.00..19947.73 rows=7391 width=0)

The estimates are too high.

You could try experimenting with

SET RANDOM_PAGE_COST TO x.x;

from the page above

RANDOM_PAGE_COST (floating point)

Sets the query optimizer's estimate of the cost of a
nonsequentially fetched disk page.
this is measured as a multiple of the cost of a sequential page
fetch.

Note: Unfortunately, there is no well-defined method of
determining ideal values for
the family of "COST" variables that were just described. You are
encouraged to
experiment and share your findings.

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