Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How a single PostgreSQL config change improved slow query performance by 50x

amplitude.engineering

Post reply on HN
Print view View original

11–20 of 46 posts

Re: How a single PostgreSQL config change improved slow query performance by 50x

#11

Post by idontgetproton »

quizotic wrote: post #3

The 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 inde…

You're saying that during sequential scans, the time it takes per row is O(n) where n is the number of columns? I find that hard to believe. Can anyone confirm / deny this?

Re: How a single PostgreSQL config change improved slow query performance by 50x

#12

Post by jve »

quizotic wrote: post #4
danjoc wrote: post #2

Thanks 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.

SQL Server 2017 has introduced 2 Automatic database tuning features: Automatic plan correction and Automatic index management (Latter for Azure SQL Only)

https://docs.microsoft.com/en-us/sql/relational-databases/au...

Re: How a single PostgreSQL config change improved slow query performance by 50x

#13

Post by olavgg »

I always increase the seq scan cost, but another thing that helped me more was updating the table statistics. In that way you can make the planner better aware of your indexes.

In my case I increased STATISTICS to 5000 and the planner immediately start using the index instead of full table scan.

https://blog.pgaddict.com/posts/common-issues-with-planner-s...

Re: How a single PostgreSQL config change improved slow query performance by 50x

#14

Post by matt4077 »

dmichulke wrote: post #8
danjoc wrote: post #2

Thanks 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 c…

There was an article and Software posted here about automatic tuning of pg with machine learning. Sorry for not searching—I’m on the road.

IIRC it was an academic paper and the process was somewhat byzantine when I tried to recreate it. But the results looked good.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#15

Post by rev112 »

quizotic wrote: post #4
danjoc wrote: post #2

Thanks 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.

This looks quite relevant: https://aws.amazon.com/blogs/ai/tuning-your-dbms-automatical... ("Tuning Your DBMS Automatically with Machine Learning")

The authors propose a way to do some automatic tuning for MySQL and Postgres.

Link to the paper itself: http://db.cs.cmu.edu/papers/2017/tuning-sigmod2017.pdf

Re: How a single PostgreSQL config change improved slow query performance by 50x

#16

Post by viraptor »

dmichulke wrote: post #8
danjoc wrote: post #2

Thanks 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 c…

I suspect it was the paper about Ottertune: http://ottertune.cs.cmu.edu (that was innodb, not pg though)

Re: How a single PostgreSQL config change improved slow query performance by 50x

#17

Post by power »

quizotic wrote: post #4
danjoc wrote: post #2

Thanks 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.

Postgres has a query optimizer that uses Genetic Algorithms: https://www.postgresql.org/docs/current/static/geqo.html

Clarification: this is for planning how to execute a query, not for tuning the db settings

Re: How a single PostgreSQL config change improved slow query performance by 50x

#18

Post by mnw21cam »

olavgg wrote: post #13

I always increase the seq scan cost, but another thing that helped me more was updating the table statistics. In that way you can make the planner better aware of your indexes. In my case I increased STATISTICS to 5000 and the planner immediately start using the index instead of full table scan. https://blog.pgaddict.com/posts/common-issues-with-planner-s...

Agreed. The query planner always has to have up-to-date and sufficiently detailed statistics in order to decide the best plan.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#19

Post by pg314 »

idontgetproton wrote: post #11
quizotic wrote: post #3

The 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 inde…

You're saying that during sequential scans, the time it takes per row is O(n) where n is the number of columns? I find that hard to believe. Can anyone confirm / deny this?

The number of columns in PostgreSQL is limited to 250-1600 [1] since a tuple (a row) can't span more than one page of memory. Since O-notation talks about asymptotic behavior, it doesn't really apply here.

But yes, tables with more columns normally take more time to scan sequentially. The complete tuple is always loaded (excluding the data of TOAST [2] attributes), there is no way to only load one column. This is one of the reasons that column-oriented databases can be faster than row-oriented databases [3].

[1] https://www.postgresql.org/message-id/42C3C382.5020108@cinec... [2] https://www.postgresql.org/docs/9.5/static/storage-toast.htm... [3] https://en.wikipedia.org/wiki/Column-oriented_DBMS

Re: How a single PostgreSQL config change improved slow query performance by 50x

#20

Post by yipenghuang »

idontgetproton wrote: post #11
quizotic wrote: post #3

The 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 inde…

You're saying that during sequential scans, the time it takes per row is O(n) where n is the number of columns? I find that hard to believe. Can anyone confirm / deny this?

The time complexity for sequential scan takes O(r), because every row must be read once. Of course rows are made up of columns so you can also specify it as O(rc) where c is the average column length.
Post reply on HN