The following bug has been logged online:
Bug reference: 3657
Logged by: Tiago Daniel Jacobs
Email address: tiago@mdtestudio.com.br
PostgreSQL version: 8.2.4
Operating system: Linux
Description: Performance leaks when using between of two equal dates
Details:
Hi, please analyze this case. If the type of the field is date, and the
dates used in between are equals, why not "convert" to equal operator
internally?
type of data_dt_Data is date
-- The slow way
EXPLAIN ANALYZE select * from agregados.agreg_sig where data_dt_Data between
'20070901' and '20070901';
"Result (cost=0.00..29.05 rows=4 width=84) (actual time=7.146..5234.762
rows=178866 loops=1)"
" -> Append (cost=0.00..29.05 rows=4 width=84) (actual
time=7.144..5052.830 rows=178866 loops=1)"
" -> Seq Scan on agreg_sig (cost=0.00..20.35 rows=3 width=84)
(actual time=0.000..0.000 rows=0 loops=1)"
" Filter: ((data_dt_data >= '2007-09-01'::date) AND
(data_dt_data <= '2007-09-01'::date))"
" -> 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))"
"Total runtime: 5298.566 ms"
-- The fast way
EXPLAIN ANALYZE select * from agregados.agreg_sig where data_dt_Data =
'20070901' ;
"Result (cost=0.00..65148.76 rows=157754 width=84) (actual
time=38.911..548.142 rows=178866 loops=1)"
" -> Append (cost=0.00..65148.76 rows=157754 width=84) (actual
time=38.907..385.408 rows=178866 loops=1)"
" -> Seq Scan on agreg_sig (cost=0.00..18.62 rows=3 width=84)
(actual time=0.001..0.001 rows=0 loops=1)"
" Filter: (data_dt_data = '2007-09-01'::date)"
" -> Bitmap Heap Scan on agreg_sig_2007_09 agreg_sig
(cost=2607.25..65130.14 rows=157751 width=59) (actual time=38.904..271.161
rows=178866 loops=1)"
" Recheck Cond: (data_dt_data = '2007-09-01'::date)"
" -> Bitmap Index Scan on idx_agreg_sig_2007_09__data_dt_data
(cost=0.00..2567.81 rows=157751 width=0) (actual time=28.348..28.348
rows=178866 loops=1)"
" Index Cond: (data_dt_data = '2007-09-01'::date)"
"Total runtime: 604.238 ms"
Best Regards,
Tiago