From: Glyn Astill <glynastill@yahoo.co.uk>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Index scan cost calculation
Date: Mon, 30 Nov 2015 14:03:21 +0000 (UTC)
Message-ID: <1322503558.4427005.1448892201990.JavaMail.yahoo@mail.yahoo.com> (raw)
In-Reply-To: <CAMkU=1zy8syr9NkkJR+-d39Gjyg6uTdCvUqfOicvmjmL1EXTOQ@mail.gmail.com>
References: <CAMkU=1zy8syr9NkkJR+-d39Gjyg6uTdCvUqfOicvmjmL1EXTOQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> From: Jeff Janes <jeff.janes@gmail.com>
> To: Glyn Astill <glynastill@yahoo.co.uk>
> Cc: Pgsql-performance <pgsql-performance@postgresql.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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: glynastill@yahoo.co.uk, jeff.janes@gmail.com
Subject: Re: Index scan cost calculation
In-Reply-To: <1322503558.4427005.1448892201990.JavaMail.yahoo@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox