hubert depesz lubaczewski <depesz@xxxxxxxxxx> writes: > version with disabled bitmapscans: > $ explain analyze select count(*) from objects where state='active' and ending_tsz <= now() - '1 day'::interval; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=10815555.72..10815555.73 rows=1 width=0) (actual time=1416200.548..1416200.548 rows=1 loops=1) > -> Seq Scan on objects (cost=0.00..10795673.36 rows=7952943 width=0) (actual time=1210074.356..1416200.498 rows=13 loops=1) > Filter: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 day'::interval))) > Total runtime: 1416200.678 ms > (4 rows) Hmm, so it's not using the index at all here. The problem clearly is that the rowcount estimate is still completely bogus :-(, even though it's presumably getting a reasonable estimate now for the ending_tsz test in isolation. I suppose what's going on here is that the "state" and "ending_tsz" columns are highly correlated, such that there are lots of 'active' items but hardly any of them ended more than a day ago? If so, you're going to have to rethink the representation somehow to get good results, because there's no way the planner will see this until we have cross-column stats in some form. The least invasive fix that I can think of offhand is to set up an index (non-partial) on the expression case when state = 'active' then ending_tsz else null end and phrase the query as WHERE (case when state = 'active' then ending_tsz else null end) <= (now() - '1 day'::interval) This should result in condensing the stats about active items' ending_tsz into a format the planner can deal with, assuming you're running a PG version that will keep and use stats on expression indexes. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
