Re: Optimizer bug??

Gaetano,

I've changed my settings as :

#fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
#effective_cache_size = 1000 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch cost
cpu_tuple_cost = 0.009 # (same)
cpu_index_tuple_cost = 0.0009 # (same)
cpu_operator_cost = 0.0025 # (same)
# fsync, fdatasync, open_sync, or
open_datasync
#wal_buffers = 8 # min 4, 8KB each

But it still doesn't optimize for that range.
Finally, i've set seq_scan off and, it works now.
But i think, there must be a way to handle those settings automatically for
cpu, ram and hdd settings(is it a sweet dream??)

Thanks
Ismail Kizir

----- Original Message -----
From: "Gaetano Mendola" <mendola(at)bigfoot(dot)com>
Newsgroups: comp.databases.postgresql.hackers
To: "Ismail Kizir" <ikizir(at)tumgazeteler(dot)com>
Sent: Tuesday, May 25, 2004 12:47 AM
Subject: 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>