From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Yuri Kunde Schlesner <yuriks+lists(at)yuriks(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Plan uses wrong index, preferring to scan pkey index instead |
Date: | 2014-11-16 17:18:46 |
Message-ID: | 2542.1416158326@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Yuri Kunde Schlesner <yuriks+lists(at)yuriks(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Johansen | 2014-11-16 22:25:45 | Re: Partitions and work_mem? |
Previous Message | Yuri Kunde Schlesner | 2014-11-15 23:24:04 | Re: Plan uses wrong index, preferring to scan pkey index instead |