Re: BASH script for collecting analyze-related info

On Wed, Oct 16, 2013 at 12:20 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:

First and foremost (and primarily directed to people who are kind enough to provide help on this list), is a script like this worthwhile?  Will it help get better problem reports and save back-and-forth time of "please post x, y and z?"  If not, I don't see the point of pursuing this.

Given the amount of back-and-forth that's already happened in this thread, it looks like the script meets at least some needs.

(Quoting myself.)  In an ideal situation, I'd see this being built into postgres, so that you could do something like EXPLAIN [ANALYZE] DESCRIBE ..., and get your descriptions directly as part of the output.  If that is pure fantasy, is there any way to directly identify all the tables, views and indexes that are involved in a query plan?

 

If it is worthwhile, what's the best way of going about getting the necessary information?  Parsing the contents of EXPLAIN output that wasn't designed for this purpose seems doable, but clunky at best.  And it doesn't tell you which views are involved.

Well, EXPLAIN can actually output in a couple of more machine-readable formats (XML, JSON, and YAML), which would be a good place to start. I'm not sure what the best way to get view information would be (aside from potentially parsing the query itself), but I'm sure someone has an idea.

If that is pure fantasy, is there any way to directly identify all the tables, views and indexes that are involved in a query plan? 

Tables and indexes you can get from explaining with a machine-readable format, then traversing the tree looking for 'Relation Name' or 'Index Name' attributes.

If no to the above, then parsing the analyze output seems the only option.  I was pondering what language a script to do this should be written in, and I'm thinking that writing it in pgpsql might be the best option, since it would be the most portable and presumably available on all systems.  I haven't fully thought that through, so I'm wondering if anyone sees reasons that wouldn't work, or if some other language would be a better or more natural choice.

I'm a proponent of just taking what you have and publishing it. That lets people use it who need it now, and makes it easier for others to improve it. 

If there's a need for a cross-platform version, that's more likely to happen if people who use that platform can test that script for you.

Notably, publishing what you have now won't in any way prevent you from rewriting it in a plpgsql function and packaging that up on pgxn later.