11–20 of 62 posts
In the "possibly unknown Postgres tools" category, I'm also a huge fan of PG Hero [0]. Gives you a nice sortable UI to look at running queries, see what the longest ones are, the most frequently called, etc. This is all just reading from pg_stats and such but the UI is very nice. [0] https://github.com/ankane/pghero
Maybe add [2008] to the title: https://www.depesz.com/2008/12/04/explaindepeszcom/
I am a bit frustrated with one question regarding explain cost. Namely how to map the cost to wall time. Based on my research so far, andwer is about "you can't". But that is difficult for me to understand. I mean, I do understand that it does not map with high (or even relatively low) accuracy. But the cases where I have been needing the information are more like me wondering after 15 minutes of runtime whether the…
But most of those things I mentioned aren't constant. Available resources/system load changes, number of rows in tables change, and # of locks and length of holding on rows/tables change. So what you're left with is a cost that reflects the relative performance of a query compared to the same result set obtained by a different query.
For reference, in Oracle, this SO link [1] explains how cost is calculated.
Note: this is all my understanding, and someone more knowledgeable might know better.
I am a bit frustrated with one question regarding explain cost. Namely how to map the cost to wall time. Based on my research so far, andwer is about "you can't". But that is difficult for me to understand. I mean, I do understand that it does not map with high (or even relatively low) accuracy. But the cases where I have been needing the information are more like me wondering after 15 minutes of runtime whether the…
Actual run time is heavily dependent on distribution of data. There may be characteristics of your data not modelled in statistics used to calculate costs.
If I were running a query that might take 15 minutes, I'd break it down, and put limit clauses in pinch points to figure out which bits of the query are sensitive (e.g. how much run time changes with increments to limits).
But often just looking at the plan will tell me if the database is doing joins in the order I think would be more or less efficient, given the extra context I have on the data distribution.
In the "possibly unknown Postgres tools" category, I'm also a huge fan of PG Hero [0]. Gives you a nice sortable UI to look at running queries, see what the longest ones are, the most frequently called, etc. This is all just reading from pg_stats and such but the UI is very nice. [0] https://github.com/ankane/pghero
pgAdmin has a really good EXPLAIN visualisation tool built-in which makes it into a neat little diagram
I do wish that it was as polished as the database core itself seems to be.
pgAdmin has a really good EXPLAIN visualisation tool built-in which makes it into a neat little diagram
pgAdmin by itself is a _really_ horrible program to use otherwise though. I do wish that it was as polished as the database core itself seems to be.