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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
