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