From: Yuri Kunde Schlesner <yuriks+lists@yuriks.net>
To: pgsql-performance@postgresql.org
Subject: Re: Plan uses wrong index, preferring to scan pkey index instead
Date: Tue, 18 Nov 2014 22:02:34 -0200
Message-ID: <1416355354.465985.192652933.31058871@webmail.messagingengine.com> (raw)
In-Reply-To: <2542.1416158326@sss.pgh.pa.us>
References: <1416093360.655916.191452721.03160196@webmail.messagingengine.com>
<2542.1416158326@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Sun, Nov 16, 2014, at 03:18 PM, Tom Lane wrote:
> I suspect that the reason the planner likes the backlog_pkey is that it's
> almost perfectly correlated with table order, which greatly reduces the
> number of table fetches that need to happen over the course of a
> indexscan
> compared to using the less-well-correlated bufferid+messageid index.
> So that way is estimated to be cheaper than using the less-correlated
> index ... and that may even be true except for outlier bufferid values
> with no recent messages.
Indeed, and I can imagine that this is more advantageous in the general
case, as I described in my last message. The problem is that the
variance is too high, with a 500x slowdown between the best and the
worst cases for that plan.
> What I think might be a workable solution, assuming you can stand a
> little
> downtime to do it, is to CLUSTER the table on the bufferid+messageid
> index. This would reverse the correlation advantage and thereby solve
> your problem. Now, ordinarily CLUSTER is only a temporary solution
> because the cluster-induced ordering degrades over time. But I think it
> would likely be a very long time until you accumulate so many new
> messages
> that the table as a whole looks well-correlated on messageid alone.
I tried this and it seems to have solved my problem! The better plan is
consistently chosen now, and it's as fast as former plan on the fast
cases, and much faster on the slow case. I will continue monitoring the
DB to see if it eventually switches back to the former scheme, and if it
does I can just include a re-cluster on my maintenance schedule. Thanks
so much for the suggestion.
--
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: yuriks+lists@yuriks.net
Subject: Re: Plan uses wrong index, preferring to scan pkey index instead
In-Reply-To: <1416355354.465985.192652933.31058871@webmail.messagingengine.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