At Sun, 13 Sep 2015 23:21:30 +0200, Tomas Vondra <tomas.von...@2ndquadrant.com> 
wrote in <55f5e8da.8080...@2ndquadrant.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

> 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.


Kyotaro Horiguchi
NTT Open Source Software Center

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to