Skip to content

Instantly share code, notes, and snippets.

@peterbe

peterbe/best-explain-analyze.py

Last active June 29, 2025 17:33
Show Gist options
  • Save peterbe/966effb3f357258ddda5aa8ac385b418 to your computer and use it in GitHub Desktop.
Save peterbe/966effb3f357258ddda5aa8ac385b418 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Peter Bengtsson revised this gist Apr 26, 2018. 1 changed file with 13 additions and 9 deletions.
    22 changes: 13 additions & 9 deletions best-explain-analyze.py
    View file
    Original file line number Diff line number Diff line change
    @@ -46,16 +46,20 @@ def main(dbname, sql_file, times=10):
    execution_times.append(float(execution_time))

    def fmt(ms):
    return '{:.3f}ms'.format(ms)
    return '{:.2f}ms'.format(ms)

    print("EXECUTION TIME")
    print("\tBEST ", fmt(min(execution_times)))
    print("\tMEAN ", fmt(statistics.mean(execution_times)))
    print("\tMEDIAN ", fmt(statistics.median(execution_times)))
    print("PLANNING TIME")
    print("\tBEST ", fmt(min(planning_times)))
    print("\tMEAN ", fmt(statistics.mean(planning_times)))
    print("\tMEDIAN ", fmt(statistics.median(planning_times)))
    print("Execution time")
    print(
    "\tBEST ",
    fmt(min(execution_times)).ljust(15),
    '<-- most important number'
    )
    print("\tmean ", fmt(statistics.mean(execution_times)))
    print("\tmedian ", fmt(statistics.median(execution_times)))
    print("Planning time")
    print("\tbest ", fmt(min(planning_times)))
    print("\tmean ", fmt(statistics.mean(planning_times)))
    print("\tmedian ", fmt(statistics.median(planning_times)))


    if __name__ == '__main__':
  2. Peter Bengtsson revised this gist Apr 19, 2018. No changes.
  3. Peter Bengtsson created this gist Apr 19, 2018.
    75 changes: 75 additions & 0 deletions best-explain-analyze.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,75 @@
    #!/usr/bin/env python3

    import re
    import os
    import subprocess
    import statistics


    class NoQueryPlanOutput(Exception):
    pass


    def main(dbname, sql_file, times=10):
    if not os.path.isfile(sql_file):
    raise FileNotFoundError(sql_file)

    with open(sql_file) as f:
    sql = f.read()
    sql = '\n'.join(
    x for x in sql.splitlines()
    if not x.strip().startswith('--')
    )
    if not sql.lower().strip().startswith('explain '):
    # In case you forgot to put the 'EXPLAIN ANALYZE' before the query
    sql = 'EXPLAIN ANALYZE {}'.format(sql)

    execution_times = []
    planning_times = []
    for i in range(times):
    p = subprocess.Popen(
    ['psql', dbname],
    stdin=subprocess.PIPE,
    stdout=subprocess.PIPE,
    stderr=subprocess.PIPE,
    )
    out, err = p.communicate(sql.encode('utf-8'))
    if err:
    raise Exception(err.decode('utf-8'))
    output = out.decode('utf-8')
    if 'QUERY PLAN' not in output:
    raise NoQueryPlanOutput(output)

    planning_time, = re.findall('Planning time: ([\d\.]+) ms', output)
    execution_time, = re.findall('Execution time: ([\d\.]+) ms', output)
    planning_times.append(float(planning_time))
    execution_times.append(float(execution_time))

    def fmt(ms):
    return '{:.3f}ms'.format(ms)

    print("EXECUTION TIME")
    print("\tBEST ", fmt(min(execution_times)))
    print("\tMEAN ", fmt(statistics.mean(execution_times)))
    print("\tMEDIAN ", fmt(statistics.median(execution_times)))
    print("PLANNING TIME")
    print("\tBEST ", fmt(min(planning_times)))
    print("\tMEAN ", fmt(statistics.mean(planning_times)))
    print("\tMEDIAN ", fmt(statistics.median(planning_times)))


    if __name__ == '__main__':
    import argparse

    parser = argparse.ArgumentParser(description='Best EXPLAIN ANALYZE')
    parser.add_argument('dbname', type=str,
    help='Database name (e.g. `psql $this`)')
    parser.add_argument('sql_file', type=str,
    help='The .sql file (e.g. `psql $dbname < $this`)')
    parser.add_argument('--times', dest='times', type=int,
    default=10,
    help='Number of times to run (default 10)')

    args = parser.parse_args()
    args = vars(args)
    main(args.pop('dbname'), args.pop('sql_file'), **args)