Re: PATCH: index-only scans with partial indexes

On 09/14/2015 09:35 AM, Kyotaro HORIGUCHI wrote:
> Hi,
,,,
>> Which is exactly the difference between costs from amcostestimate
>>
>> idx1: 4769.115000 + 0.015 * 297823 = 9236.460000
>> idx2: 6258.230000 + 0.010 * 297823 = 9236.460000
>
> These calculations are exactly right, but you overlooked the
> breakedown of indexTotalCost for idx2.
>
>> Sppoky! Although it seems like a mere coincidence, thanks to the nice
>> round numbers of tuples in the table, and lucky choice of two
>> conditions.
>
> As said above, it is not a conincidence. The exactly same
> calculation about baserestrictinfo is simply calculated in
> different places, cost_index for the former and
> btcostestiamte(genericcostestimate) for the latter.

By "coincidence" I meant that we happened to choose such a number of
conditions in the index predicate & query that this perfect match is
possible. Apparently there are two places that manipulate the costs and
in this particular case happen to perfectly compensate the effects.

As demonstrated by the example with a single condition, the costs may
actually differ for different numbers of clauses (e.g. using a single
clause makes the wider index - unexpectedly - cheaper).

>
> We should properly ignore or remove the implicitly-applied quals
> for partial indexes on cost estimation.

Probably. So far I've traced the difference to build_index_paths() where
we build index_clauses by iterating over index columns - the smaller
index does not have the column from the predicate, so we don't add the
clause. I'm not particularly familiar with this part of the code, so I
wonder where's the best place to fix this, though.

regards

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