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

Tiago Daniel Jacobs wrote:

> 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"

Please do ANALYZE agregados.agreg_sig and try the query again.  The
indexscan is grossly misestimated.

FWIW, for performance questions you should be using the
pgsql-performance list, not the bug form.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.