BUG #3657: Performance leaks when using between of two equal dates - Mailing list pgsql-bugs

From Tiago Daniel Jacobs
Subject BUG #3657: Performance leaks when using between of two equal dates
Date
Msg-id 200710051504.l95F4r3U041314@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #3657: Performance leaks when using between of two equal dates
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: tomas@tuxteam.de
Date:
Subject: Re: BUG #3654: Inconsistent handling of usernames
Next
From: Alvaro Herrera
Date:
Subject: Re: BUG #3657: Performance leaks when using between of two equal dates