Thread: BASH script for collecting analyze-related info

I just sent off to this list for query help, and found the process of gathering all the requested info somewhat tedious.  So I created a little BASH script to try to pull together as much of this information as possible.  

The script reads an analyze file, and generates SQL queries to retrieve the following information:

• Postgres Version
  
• Changes to postgresql.conf
  
• Description of all tables scanned
  
• Description of all Indices
  
• Actual and estimated row counts for all the tables
  

Hopefully this may be of use to others as well.  This list doesn't include definitions for any views or custom functions--I don't think they're in the analyze output, but if they are please let me know.

Any comments or suggestions for improvement would be most welcome.  Thanks.

Ken

p.s.,  This script runs fine on my computer (Ubuntu 13.04), but on a Fedora 11 machine it dies with 

pg_analyze_info.sh: line 18: unexpected EOF while looking for matching `)'

pg_analyze_info.sh: line 57: syntax error: unexpected end of file

If anyone knows why, or encounters a similar error and fixes it, please let me know!

--

AGENCY Software  

A data system that puts you in control

100% Free Software

http://agency-software.org/

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

p.s.,  This script runs fine on my computer (Ubuntu 13.04), but on a Fedora 11 machine it dies with 

pg_analyze_info.sh: line 18: unexpected EOF while looking for matching `)'

pg_analyze_info.sh: line 57: syntax error: unexpected end of file

If anyone knows why, or encounters a similar error and fixes it, please let me know!

It's the blank line on line 26.  Put a backslash on that line or delete it entirely.

Craig

Well that made perfect sense, but after making that change I'm still getting the same error.

3. When extracting table names you are missing:
   a. Index Scan Backward
   b. Bitmap Heap Scan
4. When extracting index names, you're missing Index Only Scans and Index Scan Backwards.

5. The whole script will fail if you're using table names with spaces (not that
   I think this is sane, but the script should recognize it)

2. why is there union with nulls in the last query?

1. instead of: "SELECT 'Postgres Version';" it's better to use \echo Postgres Version

That is - I removed the "\" at the end - it's of no use.

6. It's generally better to use
   if [[ ...
   than
   if [ ...
   reason - [[ is internal for bash, while [ is fork to external program
7. instead of | sort | uniq, it's better to use sort -u

8. usage of all-upper-case variables in bash is (by some, more
   bash-skilled people, like on #bash on irc.freenode) frowned upon.
   all-uppercase is supposed to be for environment variables only.

All in all - looks pretty good.

(off-list)

I doubt I'm the first to ask, but have you considered putting this up on github (or similar) so others can contribute more easily and keep it up-to-date?

If that's not in the cards, are you opposed to someone else putting it up on github and taking over management of it?

-john

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.