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