pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Statistics issue 2+ messages / 2 participants [nested] [flat]
* Statistics issue @ 2008-05-30 11:02 Vlad Arkhipov <arhipov@dc.baikal.ru> 0 siblings, 1 reply; 2+ messages in thread From: Vlad Arkhipov @ 2008-05-30 11:02 UTC (permalink / raw) To: pgsql-performance I have a big table that is used in many queries. Most used index is created on date field. Number of records in this table when date field is saturday is about 5 times smaller than other days, on sunday this number is always 0. Statistics target is 1000. Many queries have problems when condition on this table looks like "d between '2007-05-12' and '2007-05-12'" (saturday). EXPLAIN ANALYZE SELECT * FROM i WHERE d BETWEEN '2007-05-12' AND '2007-05-12' Index Scan using i_d on i (cost=0.00..2.39 rows=1 width=402) (actual time=0.053..4.284 rows=1721 loops=1) Index Cond: ((d >= '2007-05-12'::date) AND (d <= '2007-05-12'::date)) Total runtime: 6.645 ms EXPLAIN ANALYZE SELECT * FROM i WHERE d = '2007-05-12' Index Scan using i_d on i (cost=0.00..38.97 rows=1572 width=402) (actual time=0.044..4.250 rows=1721 loops=1) Index Cond: (d = '2007-05-12'::date) Total runtime: 6.619 ms Is there a way to solve this problem? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Statistics issue @ 2008-05-31 15:51 Tom Lane <tgl@sss.pgh.pa.us> parent: Vlad Arkhipov <arhipov@dc.baikal.ru> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2008-05-31 15:51 UTC (permalink / raw) To: Vlad Arkhipov <arhipov@dc.baikal.ru>; +Cc: pgsql-performance Vlad Arkhipov <arhipov@dc.baikal.ru> writes: > EXPLAIN ANALYZE > SELECT * > FROM i > WHERE d BETWEEN '2007-05-12' AND '2007-05-12' > Index Scan using i_d on i (cost=0.00..2.39 rows=1 width=402) (actual > time=0.053..4.284 rows=1721 loops=1) > Index Cond: ((d >= '2007-05-12'::date) AND (d <= '2007-05-12'::date)) > Total runtime: 6.645 ms > EXPLAIN ANALYZE > SELECT * > FROM i > WHERE d = '2007-05-12' > Index Scan using i_d on i (cost=0.00..38.97 rows=1572 width=402) > (actual time=0.044..4.250 rows=1721 loops=1) > Index Cond: (d = '2007-05-12'::date) > Total runtime: 6.619 ms Hmm, I wonder whether we shouldn't do something like this http://archives.postgresql.org/pgsql-committers/2008-03/msg00128.php for all range conditions, not just those made up by prefix_selectivity(). regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-05-31 15:51 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-05-30 11:02 Statistics issue Vlad Arkhipov <arhipov@dc.baikal.ru> 2008-05-31 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox