From: Tom Lane <tgl@sss.pgh.pa.us>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Index scan cost calculation
Date: Thu, 26 Nov 2015 11:44:51 -0500
Message-ID: <31151.1448556291@sss.pgh.pa.us> (raw)
In-Reply-To: <1077451263.18070016.1448554301343.JavaMail.yahoo@mail.yahoo.com>
References: <1077451263.18070016.1448554301343.JavaMail.yahoo.ref@mail.yahoo.com>
<1077451263.18070016.1448554301343.JavaMail.yahoo@mail.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Glyn Astill <glynastill@yahoo.co.uk> 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@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: tgl@sss.pgh.pa.us, glynastill@yahoo.co.uk
Subject: Re: Index scan cost calculation
In-Reply-To: <31151.1448556291@sss.pgh.pa.us>
* 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