Re: PATCH: index-only scans with partial indexes

Hello,

On 10/09/2015 02:59 AM, Kyotaro HORIGUCHI wrote:
>>> The cause of this seeming mismatch would be the place to hold
>>> indexrinfos. It is determined only by baserestrictinfo and
>>> indpred. Any other components are not involved. So IndexClauseSet
>>> is found not to be the best place after all, I suppose.
>>>
>>> Instead, I came to think that the better place is
>>> IndexOptInfo. Partial indexes are examined in check_partial_index
>>> and it seems to be the most proper place to check this so far.
>>
>> AFAIK there's only one IndexOptInfo instance per index, so I'm not
>> sure how would that work with queries that use the index in multiple
>> places?
>
> No matter if the index is used multiple places, indexrinfos is
> determined only with baserestrictinfos of the owner relation and
> itself's indpred, which are invariant through the following steps.

I'm probably missing something, but let's say we have a table like this:

CREATE TABLE t (a INT, b INT, c INT);
CREATE INDEX aidx ON t(c) WHERE a = 1;
CREATE INDEX bidx ON t(c) WHERE b = 2;

and then a trivial query (where each part perfectly matches one of the
indexes to allow IOS)

SELECT c FROM t WHERE a=1
UNION ALL
SELECT c FROM t WHERE b=2;

Now, let's say we move indexrinfos to IndexOptInfo - how will that look
like for each index? There's only a single IndexOptInfo for each index,
so it will have to work with union of all baserestrictinfos.

So we'll have these indexrinfos:

aidx: {b=2}
bidx: {a=1}

which makes index only scans unusable.

I think we effectively need a separate list of "not implied" clauses per
index-baserel combination. Maybe IndexClauseSet is not the right place,
but I don't see how IndexOptInfo could work.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services