Comment by Izkata | Hacker News Reader

Comment by Izkata | Hacker News Reader

Parent Full thread

> I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example.

Here's one that surprised me when I found out about it years ago, because I'd never really given it thought: There's a correlation statistic on columns for how well the values in that column match the row order on disk, which can influence a few different things.

In my case a query that retrieved a ton of data with an ORDER BY was using a sort and taking like two hours to run (a data source for an ETL process) - turned out because of a really bad correlation postgres was refusing to use the index, because the random access would be even slower, so it did a table scan then sort. After figuring this out and discovering the CLUSTER command (reorders the data on disk to match an index), it did an index scan and didn't need to sort at the end, was able to start streaming results immediately, and finished the entire query in like ten minutes.

Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better.

> Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better.

I come to the opposite conclusion. Clustering a table results in an access exclusive lock, and due to MVCC the ordering isn’t permanent.

Here, you as the engineer know you’d like to use a sorted index to stream results out even if the overall query end to end is slower due to the I/O cost. In my opinion there should be a way to express this within the query.

I did test that and like I said, it was strictly worse - went from something like a 2 hour runtime to 5+ hours. Random disk access and not being able to take advantage of the disk cache really is that bad.

Streaming results doesn't mean anything if the total runtime is that much worse, it just means the overall system will take hours longer to complete. And the good version doesn't need to continuously run CLUSTER, the correlation just has to be high enough for it to be the better choice, so we settled on running it once a week - only takes like 2 minutes to run the CLUSTER.

You should be thinking of it the other way around: my final 10 minute result was the ideal situation I wanted, but when circumstances were bad for it, the postgres query planner was smart enough to tell it wouldn't work and switch to the 2-hour plan instead of blindly following the original plan and taking 5 hours.

Reply on news.ycombinator.com