Re: Optimizer bug??

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??

Try to do an explain analyze for both queries, and repeat
the explain analyze but before disable the sequential scan
( "set enable_seq_scan=off" ).

For sure what do you have is extimated cost higher, what
do you have to do is decrease that cost, try with these
values:

random_page_cost = 2.5
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.0025

these are values that are good for a modern machine.

Hackers, what about to decrease the default values for this
quantities ?

Regards
Gaetano Mendola