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 <alvherre@commandprompt.com> 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