Re: [BUGS] BUG #3657: Performance leaks when using between of two equal dates

[Tiago Daniel Jacobs]

I run analyze on the database every day. And before post this bug-report, a VACUUM FULL, REINDEX, ANALYZE on entire DB. Note that the type of field is date, so, between two equal values is exactly the same that equal operator. On my system I made a check if the two dates are equal then rewrite SQL code, but I think that the big portion of users don't do this. I would like to see it corrected, and help to make postgreSQL better. Please ask me if need more information. PS: I consider this as a BUG, since the query simply don't return. And for end users is obvious that it work as the same as "="  operator. But, if it is not a BUG, i'm so sorry! Best Regards, Tiago Tom Lane escreveu: Alvaro Herrera <[EMAIL PROTECTED]> writes: Tiago Daniel Jacobs wrote: " -> Index Scan using idx_agreg_sig_2007_09__data_dt_data on agreg_sig_2007_09 agreg_sig (cost=0.00..8.70 rows=1 width=59) (actual time=7.143..4924.607 rows=178866 loops=1)" " Index Cond: ((data_dt_data >= '2007-09-01'::date) AND (data_dt_data <= '2007-09-01'::date))" Please do ANALYZE agregados.agreg_sig and try the query again. The indexscan is grossly misestimated. Not sure that it's ANALYZE's fault. Since we currently use the same selectivity estimators for > and >= (and likewise for < and <=), we have no hope of getting edge cases correct. Most of the time the stats are crude enough that it doesn't matter, but sometimes the edge value is common and then it does matter. I've been wondering if it would be worth the trouble to introduce scalarlesel and scalargesel estimators ... regards, tom lane