Yuri Kunde Schlesner <yuriks+lists@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
