The following bug has been logged online: Bug reference: 3657 Logged by: Tiago Daniel Jacobs Email address: [EMAIL PROTECTED] 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
