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

From: Tiago Daniel Jacobs <tiago(at)mdtestudio.com.br>
Date: Fri Oct 05 2007 - 11:04:53 EDT

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

---------------------------(end of broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings Received on Fri Oct 5 11:17:42 2007