As someone whose job it is to keep peoples' PostgreSQL instances happy, this list is fairly comprehensive, and much of it is good. His advice about configuration directives towards the top of the article, however, is terrible.
In particular, work_mem: the article suggests setting it to 2-3x the size of the largest temp file you see. The thing you need to be mindful of with work_mem is that the limit is per sort. I have a process on one of my masters that periodically regenerates a materialized view. Each run leaves hundreds of mibibytes of temp files. Configured per the article's advice, it's eminently possible to exhaust physical memory on sorts (100 connections each doing 10 sorts, for example). Unfortunately, the Linux OOM-killer is naïve about postgres; it tends just to thump the postmaster. Fun times.
To the contrary, something low like 16MB is the general recommendation. You can tweak that per session, if you know you'll be doing larger sorts and don't want to spill to disk ("SET work_mem = $desired_value"), but there's no need to allocate 100s of mbytes to sort tens of tuples.
EDIT: Heed the advice about transactions under Django. At a previous gig, correcting the default behavior to leave a transaction open for sometimes days at a time reduced the amount of bloat on some hotter, but small tables, by three orders of magnitude. VACUUM can't do its job if there are transactions open to whom the dead tuples it's trying to reclaim might still be visible.
Also important, the bit about IN() clauses. A few months ago, I was given a query that hadn't completed overnight and asked to make it go faster. It contained a moderately sized (but not massive) IN() clause, which I refactored into a JOIN. That was the only change I made, after which it ran in 3.7s.
I've always understood that IN clauses are hard on query optimizers and should be re-written as correlated subqueries with EXISTS/NOT EXISTS, often for giant speedups. But I've met lots of developers who have never seen this pattern before and get scared by it. Of course if you can also re-write it as a join, that's even easier.
Just out of interest, but how large was the "moderately sized" IN() clause? I am asking, as we are looking at postgresql as an alternative to MySQL, and we have some queries currently with up to 5000 values inside IN()
I don't remember specifically, but I believe it was in the thousands to low tens of thousands of rows. It's also not consistent. I've seen larger IN() clauses that never have a problem, and smaller ones that consistently do. It's been on my very low priority to-do list to put together some demo cases for the mailing lists, because overnight to < 4s just from that little refactor isn't the greatest...
Are we talking about IN clauses that contain a correlated subquery or something the optimizer would have a hard time determining was independent of outer context?
PostgreSQL is able to convert correlated subqueries with IN () clauses into joins in most cases. My guess is that it could have been two queries refactored into one.
This is not super fast in MySQL either, compared to the alternative of creating a temporary table with your 5000 values in it and doing a join on that.
That's a problem I routinely come across, and it's frustrating, because there's no reason a self-contained IN(SELECT) should ever be slower than two-stepping it with a temporary table, or worse, two queries on the client side. But it often is.
In particular, work_mem: the article suggests setting it to 2-3x the size of the largest temp file you see. The thing you need to be mindful of with work_mem is that the limit is per sort. I have a process on one of my masters that periodically regenerates a materialized view. Each run leaves hundreds of mibibytes of temp files. Configured per the article's advice, it's eminently possible to exhaust physical memory on sorts (100 connections each doing 10 sorts, for example). Unfortunately, the Linux OOM-killer is naïve about postgres; it tends just to thump the postmaster. Fun times.
To the contrary, something low like 16MB is the general recommendation. You can tweak that per session, if you know you'll be doing larger sorts and don't want to spill to disk ("SET work_mem = $desired_value"), but there's no need to allocate 100s of mbytes to sort tens of tuples.
EDIT: Heed the advice about transactions under Django. At a previous gig, correcting the default behavior to leave a transaction open for sometimes days at a time reduced the amount of bloat on some hotter, but small tables, by three orders of magnitude. VACUUM can't do its job if there are transactions open to whom the dead tuples it's trying to reclaim might still be visible.
Also important, the bit about IN() clauses. A few months ago, I was given a query that hadn't completed overnight and asked to make it go faster. It contained a moderately sized (but not massive) IN() clause, which I refactored into a JOIN. That was the only change I made, after which it ran in 3.7s.
EDIT: clarification.