7 ms·

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

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/explain_plan_5.png http://www.postgresonline.com/images/journal/explain_plan_5.... [1] http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html http://www.postgresonline.com/journal/archives/27-Reading-Pg...

unclebucknasty 8y ago
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.
- Dangeranger 8y ago
  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 https://github.com/darold/pgbadger
  - unclebucknasty 8y ago
    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 could provide significantly more value then manually analyzing explain and log file output.
    - Dangeranger 8y ago
      Looks like there is a service similar to what you asked for, but it only applies to MySQL, PerconaDB, and MariaDB right now [0]. Maybe as PostgreSQL grows in popularity more services will target it. Or go build your own, it sounds like a viable business. [0] https://www.eversql.com/faq/ https://www.eversql.com/faq/
dtech 8y ago
pgAdmin4 has the same diagramming system. Unfortunately it wasn't up to par for quite a while and is only slowly getting better.
- Dangeranger 8y ago
  Good to know it is improving, as I was disappointed with pgAdmin4 for some time.
  - gfody 8y ago
    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)
    - Dangeranger 8y ago
      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/ https://www.postgresql.org/ftp/pgadmin/pgadmin3/v1.22.2/
      - gfody 8y ago
        if you try to connect to an 11+ server pgAdmin3 will nag you to upgrade with an endless series of error message popups
radiowave 8y ago
Agreed. Also I like that it's so quick (at least for a regular EXPLAIN) - I would very often hit F7 to see the query plan visualization, even for just a split second, before pressing F5 to run the query. If I see the table-scan icon appear somewhere when I'm not expecting it to, it's a big hint I've done something dumb.

unclebucknasty 8y ago

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.

Dangeranger 8y ago
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 https://github.com/darold/pgbadger
- unclebucknasty 8y ago
  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 could provide significantly more value then manually analyzing explain and log file output.
  - Dangeranger 8y ago
    Looks like there is a service similar to what you asked for, but it only applies to MySQL, PerconaDB, and MariaDB right now [0]. Maybe as PostgreSQL grows in popularity more services will target it. Or go build your own, it sounds like a viable business. [0] https://www.eversql.com/faq/ https://www.eversql.com/faq/

dtech 8y ago

pgAdmin4 has the same diagramming system. Unfortunately it wasn't up to par for quite a while and is only slowly getting better.

Dangeranger 8y ago
Good to know it is improving, as I was disappointed with pgAdmin4 for some time.
- gfody 8y ago
  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)
  - Dangeranger 8y ago
    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/ https://www.postgresql.org/ftp/pgadmin/pgadmin3/v1.22.2/
    - gfody 8y ago
      if you try to connect to an 11+ server pgAdmin3 will nag you to upgrade with an endless series of error message popups

radiowave 8y ago

Agreed. Also I like that it's so quick (at least for a regular EXPLAIN) - I would very often hit F7 to see the query plan visualization, even for just a split second, before pressing F5 to run the query. If I see the table-scan icon appear somewhere when I'm not expecting it to, it's a big hint I've done something dumb.