Re: Optimizer bug??

Hi All,

EXPLAIN ANALYZE
SELECT COUNT(*) AS c FROM articletbl WHERE (tarih BETWEEN '2004-04-24' AND
'2004-05-24')

result is :

QUERY PLAN
Aggregate (cost=40987.02..40987.02 rows=1 width=0) (actual
time=39209.476..39209.478 rows=1 loops=1)
-> Seq Scan on articletbl (cost=0.00..40947.39 rows=15851 width=0)
(actual time=1233.369..39153.741 rows=19515 loops=1)
Filter: ((tarih >= '2004-04-24'::date) AND (tarih <=
'2004-05-24'::date))
Total runtime: 39210.077 ms

I use fedora core 1 SMP kernel
Configuration :
Dual PIII-550
Dual SCSI (10ms. access time i think)
3x128 mb. SDRAM

Regards
Ismail Kizir
----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Ismail Kizir" <ikizir(at)tumgazeteler(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Monday, May 24, 2004 11:52 PM
Subject: Re: [HACKERS] 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.
>
>
>