On 09/14/2015 09:35 AM, Kyotaro HORIGUCHI wrote:
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

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.


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

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

Reply via email to