51–60 of 62 posts
For example, five chosen at random:
https://explain.depesz.com/s/9JNv https://explain.depesz.com/s/jRXr https://explain.depesz.com/s/cxjk https://explain.depesz.com/s/FPur https://explain.depesz.com/s/GD8
I think it should de-dupe identical plans at submission time.
Anybody know of a tool that will do something like this for AWS Redshift? Redshift is based on an old version of PostgreSQL.
I rather like the PostgreSQL Explain Vizualizer. [1]
I can keep multiple plans around, and it has a lot of detail.
My favorite tool for this kind of analysis was pgAdmin3, which had a very nice diagramming output for EXPLAIN ANALYZE which you can see here [0]. Hovering over the various images in the diagram would display their time, rows, and which statement caused them. There is also this excellent post which shows how to break down an EXPLAIN and reason about the performance [1]. [0] http://www.postgresonline.com/images/journal…
Seems a much more efficient approach than analyzing and diagramming (sometimes) complex explain results.
Earlier quoted context omitted.
Postgres' cost model uses a bunch of constants that evaluate the relative cost of queries with regard to CPU and I/O and random vs. sequential reads. You can actually tune the cost model yourself by changing the values of the constants. Cost does not really map to wall-time in any way besides the default constants being chosen relative to the cost of a sequential read as a basis (and Postgres admits these relative va…
Which reminds me to wonder - does anybody know why the cost model uses constants from a config file? Compared to all the amazing things that Postgres does, it seems like "measure the value of a sequential read and a random read at runtime, then use those values" should be pretty easy...
My favorite tool for this kind of analysis was pgAdmin3, which had a very nice diagramming output for EXPLAIN ANALYZE which you can see here [0]. Hovering over the various images in the diagram would display their time, rows, and which statement caused them. There is also this excellent post which shows how to break down an EXPLAIN and reason about the performance [1]. [0] http://www.postgresonline.com/images/journal…
Is there a tool for Postgres that will provide optimization tips over time, based on actual queries run against the DB? Seems a much more efficient approach than analyzing and diagramming (sometimes) complex explain results.
Earlier quoted context omitted.
Is there a tool for Postgres that will provide optimization tips over time, based on actual queries run against the DB? Seems a much more efficient approach than analyzing and diagramming (sometimes) complex explain results.
Probably the closest thing to what you are asking for is pgBadger [0]. If there is a better tool I'd love to hear about it. [0] https://github.com/darold/pgbadger
In other words, this could be another input for the kind of tool I'm thinking of.
Kind of surprised this doesn't exist, as it seems doable. Given a combination of query optimizers, slow query monitoring, and common tuning techniques, even a rudimentary, heuristics-based recommendation tool could provide significantly more value then manually analyzing explain and log file output.
Earlier quoted context omitted.
Probably the closest thing to what you are asking for is pgBadger [0]. If there is a better tool I'd love to hear about it. [0] https://github.com/darold/pgbadger
Thanks for the tip. Looks interesting, but still on the "analyze and do something" side vs monitoring then making suggestions. In other words, this could be another input for the kind of tool I'm thinking of. Kind of surprised this doesn't exist, as it seems doable. Given a combination of query optimizers, slow query monitoring, and common tuning techniques, even a rudimentary, heuristics-based recommendation tool co…
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…
Postgres' cost model uses a bunch of constants that evaluate the relative cost of queries with regard to CPU and I/O and random vs. sequential reads. You can actually tune the cost model yourself by changing the values of the constants. Cost does not really map to wall-time in any way besides the default constants being chosen relative to the cost of a sequential read as a basis (and Postgres admits these relative va…
This is a claim that is extremely difficult for me to believe. Because this statement says that you really can't say that a query in one system with cost of 10 is any more likely to be run faster than a query with a cost of 10^300 (these are made up numbers) in another environment. If there were way to get a mapping with even accuracy within two orders of magnitude (e.g. a query with cost x is likely to run something between 10 and 1000 minutes) on my current system, I would be perfectly happy. Even three orders of magnitude accuracy would likely be helpful.
Earlier quoted context omitted.
Postgres' cost model uses a bunch of constants that evaluate the relative cost of queries with regard to CPU and I/O and random vs. sequential reads. You can actually tune the cost model yourself by changing the values of the constants. Cost does not really map to wall-time in any way besides the default constants being chosen relative to the cost of a sequential read as a basis (and Postgres admits these relative va…
> Cost does not really map to wall-time in any way This is a claim that is extremely difficult for me to believe. Because this statement says that you really can't say that a query in one system with cost of 10 is any more likely to be run faster than a query with a cost of 10^300 (these are made up numbers) in another environment. If there were way to get a mapping with even accuracy within two orders of magnitude (…
> ...besides the default constants being chosen relative to the cost of a sequential read as a basis (and Postgres admits these relative values are not scientific or necessarily accurate)
The costs will always be relative to each other as defined by your constants. The whole point of the cost model is to compare plans so that the fastest one can be chosen. If you hyper-tune your constants according to your system (which I feel is probably pretty difficult to do accurately), you could maybe get something within the orders of magnitude you want.