Re: Index scan cost calculation

On Mon, Nov 30, 2015 at 6:03 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
>
>
>
>
> If I create the index show+best+block+row+seat then the planner appears to favour that, and all is well.  Despite the
startupcost 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
correlationbetween "best"+"block" and "type", but from Toms reply my understanding was that total selectivity for the
queryis 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