Re: PATCH: index-only scans with partial indexes

Hi,

At Sun, 13 Sep 2015 23:21:30 +0200, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote in <55F5E8DA(dot)8080303(at)2ndquadrant(dot)com>
> That's indeed strange, but after poking into that for a while, it
> seems rather like a costing issue. Let me demonstrate:
...
> Now, both plans are index only scans, but the first one has Index Cond
> and the other one does not!

The seemingly removed IndexCond qual is counted as non-index
quals at the last in cost_index. The quals that the partial index
implies should be ignored on cost_estimation.

> I've put a bunch of logging into cost_index(), and turns out that
> while the final cost is *exactly* the same, it's most likely by
> chance. After we call amcostestimate, we get these two results:

So it is *not by chance* but a stable behavior defined by
algorithm.

> It seems that the problem is related to this:
>
> qpquals
> = extract_nonindex_conditions(baserel->baserestrictinfo,
> path->indexquals);
>
> while the "larger" index on (a,b) gets
>
> path->indexquals=(b BETWEEN 300000 AND 600000)
> qpquals=NIL
>
> the "smaller" index on (a) gets
>
> path->indexquals=NIL
> qpquals=(b BETWEEN 300000 AND 600000)
>
> And so the larger index gets qpqual_cost=0, the smaller one gets
> qpqual_cost=0.005, and so cpu_per_tuple is either 0.01 or 0.015.
>
> 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.

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

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center