On Tue, Aug 09, 2011 at 04:08:39PM -0400, Tom Lane wrote:
> 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,
yes, that's correct.
> 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.
it's 8.3.11.
I solved the problem by adding "enable_bitmapscan = false" (and keeping
the query in original format, with subselect) which caused the plan to
be ok.
but I'm much more interested to understand why pg chooses *not* to use
index which is tailored specifically for the query - it has exactly
matching where clause, and it indexes the column that we use for
comparison.
the thing is - i solved the problem for now. I might add new index the
way you suggest, and it might help. but it's is very unnerving that
postgresql will just choose to ignore specially made index, perfectly
matching the criteria in query.
since I can't test it - is there any chance (Cédric suggested something
like this) that some newer version has more logic to try harder to use
best index?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
