2011/8/9 hubert depesz lubaczewski <depesz@depesz.com>:
> 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?
I wondered if it is the same logic to choose between bitmap and
indexscan in both 8.3 and HEAD.
It looks like it is (except that now you can put the not-wanted index
on another tablepsace and increase the cost of accessing it, which is
another no-so-pretty way to workaround the issue).
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact with it.
> http://depesz.com/
>
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation