Re: Optimizer bug??

On Mon, 24 May 2004, Ismail Kizir wrote:

> Hi all,
>
> > give us the result of these queries:
> > SELECT COUNT(*) FROM articletbl;
>
> 268726 records, it takes 34169 ms. to compute this
>
> > SELECT COUNT(*) AS c FROM articletbl WHERE
> > mydate BETWEEN '2004-04-24' AND '2004-05-24';
>
> 18982 records, it takes 34249 ms. to compute this.
>
> > SELECT COUNT(*) AS c FROM articletbl WHERE
> > mydate = '2004-04-24';
> 850 records, it takes only 40 ms. to compute this.
>
> It is evident that there is a problem here! Am I wrong??

What does explain analyze show for the between query (not just explain)
and what does it show if enable_seqscan is set to false? It's possible
that it's badly overestimating the cost of the range query, but that's
hard to say at this point. There is a point at which in general an index
scan becomes more costly than a sequence scan, and it's possible to move
that point by changing optimizer settings in the configuration.