Re: Index scan cost calculation

> From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
> To: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> Cc: Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
> Sent: Saturday, 28 November 2015, 19:25
> Subject: Re: [PERFORM] Index scan cost calculation
>
>
> Why does the index seats_index02 exist in the first place? It looks
> like an index designed for the benefit of a single query. In which
> case, could flag column be moved up front? That should prevent it
> from looking falsely enticing.
>
> A column named "flag" is not usually the type of thing you expect to
> see a range query on, so moving it leftward in the index should not be
> a problem.
>

Unfortunately it's not possible to move flag left in this scenario.

As you say it's an issue that would not really exist in normal SQL access. The main issue is the way it's required for ordering; The index in question is used by a legacy language that accesses records sequentially as if they were direct from isam files it used historically via a driver. In some cases it steps through records on a particular show+type until a flag changes and carries on unless particular values are seen.

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.

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. Are particular equality clauses ever excluded from the calculation as a result of available indexes or otherwise? Or is it just likely that the selection of the new index is just by chance?

Either way I my understanding here is definitely lacking.