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(at)acm(dot)org |
| P.O. Box 314 | URL: http://www.congen.com/~bruc |
| Pennington, NJ 08534 | |
+----------------------------------+------------------------------------+