Query Performance

This page, and its sub-pages, are an analysis of problems reported on pgsql-performance in 2011, 2012, and 2013. I have not analyzed all problems reported in that time period. A few problems have been attributed to more than one cause, and a few problems have been attributed to no cause at all. However, most threads I've analyzed are shown exactly once. Many of the judgements are arguable; I have applied nothing better than my own discretion. This information is provided in the hope that it will be useful and not for any other purpose; as to whether it is actually useful, your mileage may vary.

Settings. Includes anything you can fix with postgresql.conf changes, DDL, or operating systems settings changes. Generally, I included things in this category only if I felt that a settings change is the "right" fix. It would be nice if some of these settings had auto-tuning to avoid having to set them manually.

Just Plain Slow. Includes anything that amounts to an unreasonable expectation on the part of the user. These are often questions of the form “why is query A slower than query B?” when A is actually doing something much more expensive than B.

We're Bad At That. Includes anything that could be faster in some other database product, but isn't fast in PostgreSQL for some reason (not implemented yet, or architectural artifact).

Planner Error. Bad decisions about the cost of one plan vs. another plan due to limitations of the optimizer.

- Conceptual Errors. The planner isn't able to recognize that two different queries are equivalent, so it doesn't even consider the best plan.

- Row Count Estimation Errors. The planner considers the best plan, but rejects it because it appears too expensive, due to an inaccurate row count estimate.

- Cost Estimation Errors. The planner considers the best plan and estimates row counts accurately, but rejects the plan because it incorrectly estimates the cost.

Bugs. Bugs in the query planner, or in one case, the Linux kernel.