About the only thing I miss from the old Ingres database on OpenVMS was the query plan view. This page (with one of the most horrible backgrounds for viewing) http://ariel.its.unimelb.edu.au/~yuan/Ingres/us_38697.html has some examples of what I remember. Seeing the letters FSM still fills me with dread.
PostgreSQL's Explain Analyze Made Readable
41–50 of 62 posts
Re: PostgreSQL's Explain Analyze Made Readable
#42Earlier quoted context omitted.
pgAdmin4 has the same diagramming system. Unfortunately it wasn't up to par for quite a while and is only slowly getting better.
Good to know it is improving, as I was disappointed with pgAdmin4 for some time.
pgAdmin4 has become 100x better since the first preview was released - unfortunately it's still a web client and struggles with "large" data sets (I wouldn't call a million rows large, pgAdmin3 would return it in a snap, you could copy it to your clipboard, etc., pgAdmin4 wheezes under the weight of its webshit infrastructure)
Re: PostgreSQL's Explain Analyze Made Readable
#43This is indeed an awesome tool. We've used it quite a bit for local profiling of known slow queries.
FYI because I wish someone had pointed this out to me when I stumbled across tools like this: For profiling really busy production DBs, I highly recommend https://pganalyze.com/ Not affiliated - just an ecstatic customer.
Re: PostgreSQL's Explain Analyze Made Readable
#44I 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…
Isn’t this what EXPLAIN ANALYZE is for though?
That actually runs the query, so in GP's situation where it isn't returning, that won't either.
Re: PostgreSQL's Explain Analyze Made Readable
#45Earlier quoted context omitted.
Good to know it is improving, as I was disappointed with pgAdmin4 for some time.
pgAdmin4 has become 100x better since the first preview was released - unfortunately it's still a web client and struggles with "large" data sets (I wouldn't call a million rows large, pgAdmin3 would return it in a snap, you could copy it to your clipboard, etc., pgAdmin4 wheezes under the weight of its webshit infrastructure)
It may be worth noting that while pgAdmin3 is officially deprecated, the binaries can still be downloaded and used. Latest version is 1.22.2 from November of 2016 [0].
[0] https://www.postgresql.org/ftp/pgadmin/pgadmin3/v1.22.2/
Re: PostgreSQL's Explain Analyze Made Readable
#46Earlier quoted context omitted.
pgAdmin4 has become 100x better since the first preview was released - unfortunately it's still a web client and struggles with "large" data sets (I wouldn't call a million rows large, pgAdmin3 would return it in a snap, you could copy it to your clipboard, etc., pgAdmin4 wheezes under the weight of its webshit infrastructure)
It may be worth noting that while pgAdmin3 is officially deprecated, the binaries can still be downloaded and used. Latest version is 1.22.2 from November of 2016 [0]. [0] https://www.postgresql.org/ftp/pgadmin/pgadmin3/v1.22.2/
if you try to connect to an 11+ server pgAdmin3 will nag you to upgrade with an endless series of error message popups
Re: PostgreSQL's Explain Analyze Made Readable
#47Earlier quoted context omitted.
Isn’t this what EXPLAIN ANALYZE is for though?
That actually runs the query, so in GP's situation where it isn't returning, that won't either.
At least in my experience, things being slow is almost always due to a table scan rather than using an index.
Just the estimates (ie not actually running the query) should give clues as to which table it scans, and the predicates gives hints as to which index would have helped.
Not always easy fix though, sometimes what you're asking for just isn't possible to index in a nice way. Just the other day me and a colleague was trying to optimize a query, and we couldn't get it to use the index simply because the selectivity was too low. The most selective part of the where clause was a non-trivial and highly dynamic "exists" sub-query... We ended up using a few materialized views with manual refresh but still the query took 2-3 seconds (down from 10-15).
Re: PostgreSQL's Explain Analyze Made Readable
#48I 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 values are not scientific or necessarily accurate).
Re: PostgreSQL's Explain Analyze Made Readable
#49If it's better, why not submit a pull request to PostGres and try to make it the default output style?
Re: PostgreSQL's Explain Analyze Made Readable
#50I 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…
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...