Bulk optimization of queries in Postgres
substack.com
substack.com
If you’re working with Postgres you owe it to yourself to understand how to read query planner output.
Would you care to comment on what any aspiring writers should pay attention to in particular, to make your reading more enjoyable?
> If you’re working with Postgres you owe it to yourself to understand how to read query planner output.
That's good and all, but sometimes having more user friendly visualization of the same data, or useful summaries is lovely.
If you were reading it uncharitably, you might say that it seems like the authors don't understand Postgres that well. A more charitable reading is it's presented as a journey of discovery that they expect their readers to have been on, but it's hard to tell.
> Well, now when we have this job done and finally open the log file to find out… a long wall of plain text. It actually gives us no info about the query plan except for its execution time.
As someone who has spent a fair amount of time with the PG planner there were a bunch of things in there that felt off. Eg in the below my guess would be that this is to do with paging, not statistics.
> By the time you retrieve the query from log files and run it again, the dataset and statistics will be completely different. So when you finally execute the query for debugging, it might run quickly this time, which makes you wonder why it was slow before.
If someone is new to query optimisation in PG this article isn't a great place to start. There's a lot of content that reads like the author doesn't know Postgres well but, given that they've written a tool to visualise pg queries, I'm going to assume that they do and instead the article just doesn't quite hit the mark.