Hi, 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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
