Re: Index scan cost calculation

Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> writes:
> Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and trying to figure out why a particular index is being chosen over another for updates/deletes.
> From what I can see the reason is that plans using either index have the same exactly the same cost. So rather I'm asking if there's something glaringly obvious I'm missing, or is there anything I can to to get better estimates.

I think what's happening is that it's estimating that exactly one index
tuple needs to be visited in both cases, so that the cost estimates come
out the same. That's correct in the one case but overly optimistic in the
other; the misestimate likely is a consequence of the index columns being
interdependent. For instance, if "type" can be predicted from the other
columns then specifying it isn't really adding anything to the query
selectivity, but the planner won't know that. We can conclude from the
results you've shown that the planner thinks that show+type+best+block
is sufficient to uniquely determine a table entry, which implies that
at least some of those columns are strongly correlated with row+seat.

The problem will probably go away by itself as your table grows, but
if you don't want to wait, you might want to reflect on which of the index
columns might be (partially?) functionally dependent on the other columns,
and whether you could redesign the key structure to avoid that.

regards, tom lane