On Mon, Nov 30, 2015 at 6:03 AM, Glyn Astill <glynastill@xxxxxxxxxxx> wrote: > > > > > If I create the index show+best+block+row+seat then the planner appears to favour that, and all is well. Despite the startup cost estimate being the same, and total cost being 0.01 higher. This is something I fail to understand fully. I think usually Index scans that are estimated to be within 1% of each other are considered to be identical. Which one gets chosen then depends on what order they are considered in, which I think is in implementation dependent detail. Usually it is the most recently created one, which would explain why you got the plan switch with the new index. > Tom stated the index choice is due to a selectivity underestimate. I think this may be because there is actually a correlation between "best"+"block" and "type", but from Toms reply my understanding was that total selectivity for the query is calculated as the product of the individual selectivities in the where clause. I think the problem here is not with total query selectivity estimate, but rather selectivity estimates of the indexes. It thinks the combination of (show, type, best, block) is enough to get down to a single row. One index adds "flag" to that (which is not useful to the query) and the other adds "row" to that, which is useful but the planner doesn't think it is because once you are down to a single tuple additional selectivity doesn't help. > Are particular equality clauses ever excluded from the calculation as a result of available indexes or otherwise? Clauses that can't be used in an "indexable" way are excluded from the index selectivity, but not from the total query selectivity. > Or is it just likely that the selection of the new index is just by chance? Bingo. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
