Re: Problem with planner

hubert depesz lubaczewski <depesz(at)depesz(dot)com> 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