From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Strange choice of general index over partial index
Date: Thu, 15 Jan 2015 22:03:53 -0500
Message-ID: <6448.1421377433@sss.pgh.pa.us> (raw)
In-Reply-To: <54B87820.3090101@catalyst.net.nz>
References: <54B83F70.7080004@agliodbs.com>
<54B85D32.7090106@catalyst.net.nz>
<54B87820.3090101@catalyst.net.nz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> This is with each version loading exactly the same dataset (generated by
> the attached scripty). Obviously this is a vast simplification of what
> Josh is looking at - but it is (hopefully) interesting that these later
> versions are doing so much better...
Actually, what I see when using this dataset is that both the estimated
cost and the actual runtime of the query are within a percent or so of
being the same when using either index. (Try forcing it to use the
non-preferred index by dropping the preferred one, and you'll see what
I mean.) The absolute magnitude of the cost estimate varies across
versions, but not the fact that we're getting about the same estimate
for both indexes.
I suspect the same may be true for Josh's real-world database, meaning
that the index choice is depending on phase-of-the-moon factors like
which index has the lower OID, which is doubtless contributing to
his frustration :-(
I think that the real key to this problem lies in the index bloat pattern,
which might be quite a bit different between the two indexes. This might
mean traversing many more index leaf pages in one case than the other,
which would account for the difference in real runtimes that he's seeing
and I'm not. I don't recall at the moment whether 9.2's cost estimation
rules would do a good job of accounting for such effects. (And even if
it's trying, it'd be working from an average-case estimate, which might
not have much to do with reality for this specific query.)
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, mark.kirkwood@catalyst.net.nz, josh@agliodbs.com
Subject: Re: Strange choice of general index over partial index
In-Reply-To: <6448.1421377433@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