From: Tom Lane <tgl@sss.pgh.pa.us>
To: Yuri Kunde Schlesner <yuriks+lists@yuriks.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Plan uses wrong index, preferring to scan pkey index instead
Date: Sun, 16 Nov 2014 12:18:46 -0500
Message-ID: <2542.1416158326@sss.pgh.pa.us> (raw)
In-Reply-To: <1416093360.655916.191452721.03160196@webmail.messagingengine.com>
References: <1416093360.655916.191452721.03160196@webmail.messagingengine.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Yuri Kunde Schlesner <yuriks+lists@yuriks.net> writes:
> Does anyone know if there's any tweaking I can do in Postgres so that it
> uses the appropriate plan?
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.
You could try fooling around with the planner cost parameters
(particularly random_page_cost) to see if that changes the decision;
but it's usually a bad idea to alter cost parameters on the basis of
tweaking a single query, and even more so for tweaking an outlier
case of a single query.
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.
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, yuriks+lists@yuriks.net
Subject: Re: Plan uses wrong index, preferring to scan pkey index instead
In-Reply-To: <2542.1416158326@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