How a single PostgreSQL config change improved slow query performance by 50x
amplitude.engineering
How a single PostgreSQL config change improved slow query performance by 50x
1–10 of 46 posts
Re: How a single PostgreSQL config change improved slow query performance by 50x
#2Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.
Re: How a single PostgreSQL config change improved slow query performance by 50x
#3The prescription of changing sequential_page_cost to
equal random_page_cost is certainly reasonable for SSD, but I wonder if the underlying issues aren't somewhat deeper and more interesting. One difference between a sequential scan and an index scan is the amount of data being scanned. PostgreSQL stores information horizontally as rows and a sequential scan will have to read in all column values of all rows. An index scan will read through all values of a _single_ column. The 50x performance difference _might_ be just that the whole row is 50x wider than the width of the indexed join column.
An interesting second factor relates to the nature of the SSD storage. With SSDs a read request will pull back a 4K page, even if the read request was smaller. So it's not quite right to say that a sequential read and a random read cost the same on SSD, particularly if the same 4K page must be read multiple times. I suspect that the particular index technique used by PostgreSQL tends to organize data such that successive indexed values reside in the same 4K SSD page. IOW, it's not so much that the cost of random SSD access is the same as sequential SSD access (though that's true), as it is that the PostgreSQL index mechanism doesn't require multiple reads of the same 4K page.
if a Hash-based index was used instead of a Btree-based index, and if the table width was narrower, the sequential scan might have outperformed the index scan.
Re: How a single PostgreSQL config change improved slow query performance by 50x
#4Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.
+1 for the idea of tuning query optimization based on ML. I don't know of any DBMSs that take this advice, and that's remarkable in this day and age.
Re: How a single PostgreSQL config change improved slow query performance by 50x
#5For those interested in postgresql.conf tuning, I did a recent presentation at two conferences recently, hope it's interesting. Slides: https://speakerdeck.com/ongres/postgresql-configuration-for-...
[shamless plug]
Re: How a single PostgreSQL config change improved slow query performance by 50x
#6You also get good results tweaking this particular knob if you have large amounts of RAM. If your blocks are almost always in OS cache, you are almost never going to make random seeks even if the PostgreSQL planner thinks you are.
Re: How a single PostgreSQL config change improved slow query performance by 50x
#7For those interested in postgresql.conf tuning, I did a recent presentation at two conferences recently, hope it's interesting. Slides: https://speakerdeck.com/ongres/postgresql-configuration-for-... [shamless plug]
They look great, thanks for sharing! Do you know if the talks were recorded?
Re: How a single PostgreSQL config change improved slow query performance by 50x
#8Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.
Your suggestion sounds very much like that of a Silicon Valley start-up guy ("Machine Learning to the rescue!") but I believe here a deterministic detection of the underlying drive, possibly tied to a warning on start-up, would be easier to code, less bug-prone, and faster.
OTOH, if you speak about the general problem of optimizing configuration, then I distinctly recall having read something about automatic server configuration, IMO on AWS, but quite possibly only as a feature request.
Re: How a single PostgreSQL config change improved slow query performance by 50x
#9You also get good results tweaking this particular knob if you have large amounts of RAM. If your blocks are almost always in OS cache, you are almost never going to make random seeks even if the PostgreSQL planner thinks you are.
That feels like something the database could do for itself - checking whether a range of mmap()ed blocks are actually in memory or not is a single syscall.
I guess for large indexes the overhead of walking the page tables is going to be large though, so it’s not necessarily going to be a net win.
Re: How a single PostgreSQL config change improved slow query performance by 50x
#10Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.
+1 for the idea of tuning query optimization based on ML. I don't know of any DBMSs that take this advice, and that's remarkable in this day and age.
Oracle already promised to bring something similar back in september: https://www.oracle.com/database/autonomous-database/index.ht...