[taler-grid5k] 99/189: add explain analyzer

[gnunet]

This is an automated email from the git hooks/post-receive script.

marco-boss pushed a commit to branch master
in repository grid5k.

commit 37c60b170638314fb01b8d9307f4b29b85c3e9f9
Author: Boss Marco <bossm8@bfh.ch>
AuthorDate: Sun Mar 20 10:52:58 2022 +0100

    add explain analyzer
---
 additional/explain-visualizer/explain.py | 221 +++++++++++++++++++++++++++++++
 1 file changed, 221 insertions(+)

diff --git a/additional/explain-visualizer/explain.py 
b/additional/explain-visualizer/explain.py
new file mode 100755
index 0000000..34f9404
--- /dev/null
+++ b/additional/explain-visualizer/explain.py
@@ -0,0 +1,221 @@
+#!/usr/bin/env python3
+
+from json import loads
+from re import compile
+from subprocess import check_output, CalledProcessError
+from enum import Enum
+import argparse
+
+
+class Type(Enum):
+    RELATION = 0
+    OTHER = 1
+
+
+class Plan:
+    def __init__(self, name: str, _type: Type, indent: int):
+        self.name = name
+        self.partitions_hit = 0
+        self.would_have_hit_partitions = 0
+        self.rows_estimated = 0
+        self.rows_returned = 0
+        self.time_spent = 0
+        self.scan_type = ""
+        self.used_index = ""
+        self.condition = ""
+        self.type = _type
+        self.indent = " " * indent
+
+    def print(self):
+        if self.type == Type.RELATION:
+            self._print_relation()
+        elif self.type == Type.OTHER:
+            self._print_other()
+
+    def _print_relation(self):
+        print(self.indent + f"Relation '{self.name}':")
+        if self.partitions_hit == 0:
+            print(self.indent + f"    Not touched")
+            print(self.indent + f"    Partitions would be Hit: 
{self.would_have_hit_partitions}")
+        else:
+            print(self.indent + f"    Partitions Hit: {self.partitions_hit}")
+            print(self.indent + f"    Time Spent:     {self.time_spent} ms")
+            print(self.indent + f"    Rows Estimated: {self.rows_estimated}")
+            print(self.indent + f"    Rows Returned:  {self.rows_returned}")
+            print(self.indent + f"    Scan Method:    {self.scan_type}")
+        if self.scan_type == 'Index Scan':
+            print(self.indent + f"    Index Used: {self.used_index}")
+        print("")
+
+    def _print_other(self):
+        print(self.indent + f"'{self.name}':")
+        print("")
+
+
+plans = {}
+most_time_consuming = Plan("default", Type.RELATION, 0)
+
+partitioned_re = compile('(.*)_([0-9]+|default)')
+
+args = None
+
+
+def get_explain() -> dict:
+    args.sql = args.sql.replace("\n", "").replace('"', "")
+    sql = "EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON) " + args.sql + ";"
+    try:
+        analyze_json = check_output(args=['psql',
+                                          '-U', args.db_user,
+                                          '-h', args.db_host,
+                                          '-d', args.db_name,
+                                          '-p', args.db_port,
+                                          '-P', 'pager=off',
+                                          '-qtAXc', sql],
+                                    env={'PGPASSWD': args.db_pw},
+                                    timeout=10)
+        return loads(analyze_json)[0]
+    except CalledProcessError as e:
+        print(e.output)
+        exit(1)
+
+
+def ensure_partition_added(relation, indent):
+    if relation not in plans:
+        plans[relation] = Plan(relation, Type.RELATION, indent)
+
+
+def ensure_plan_added(plan, indent):
+    idx = plan + str(indent)
+    plans[idx] = Plan(plan, Type.OTHER, indent)
+
+
+def check_most_expensive_time(plan: Plan):
+    global most_time_consuming
+    if most_time_consuming.time_spent < plan.time_spent:
+        most_time_consuming = plan
+
+
+def parse_scan(plan, indent):
+    relation = plan['Relation Name']
+
+    # Remove partition suffix
+    if partitioned_re.match(relation):
+        relation = relation.split('_')[0:-1]
+        relation = "_".join(relation)
+
+    ensure_partition_added(relation, indent)
+    _plan = plans[relation]
+
+    if plan['Actual Loops'] > 0:
+        _plan.partitions_hit += 1
+        _plan.time_spent += plan['Actual Total Time']
+        _plan.rows_estimated += plan['Plan Rows']
+        _plan.rows_returned += plan['Actual Rows']
+        check_most_expensive_time(_plan)
+    else:
+        _plan.would_have_hit_partitions += 1
+
+    if _plan.scan_type == "":
+        _plan.scan_type = plan['Node Type']
+        if _plan.scan_type == 'Index Scan':
+            _plan.used_index = plan['Index Name']
+
+
+def iterate_plans():
+    for plan in plans:
+        yield plans[plan]
+
+
+def parse_plan(plan, indent=0):
+    if 'Plans' in plan:
+        ensure_plan_added(plan['Node Type'], indent)
+        for _plan in plan['Plans']:
+            parse_plan(_plan, indent + 2)
+    else:
+        parse_scan(plan, indent + 2)
+
+
+def print_summary(attr_name):
+    total = 0
+    for plan in iterate_plans():
+        if plan.type == Type.RELATION:
+            attr = getattr(plan, attr_name)
+            total += attr
+            print(f"  {plan.name}: {attr}")
+    print(f"(Total: {total})")
+
+
+def print_non_indexed():
+    total = 0
+    for plan in iterate_plans():
+        if plan.scan_type == 'Seq Scan':
+            print(f"  {plan.name}")
+            total += 1
+    print(f"(Total: {total})")
+
+
+def run():
+    explain = get_explain()
+    plan = explain['Plan']
+
+    print("General:\n")
+    print(f"Estimated Rows:          {plan['Plan Rows']}")
+    print(f"Actual Rows Returned:    {plan['Actual Rows']}")
+    print(f"Actual Execution Time:   {plan['Actual Total Time']} ms")
+
+    parse_plan(plan)
+
+    print("\nSummary: \n")
+    print(f"Rows Returned by Sub-queries:")
+    print_summary('rows_returned')
+    print("\nPartitions Hit:")
+    print_summary('partitions_hit')
+    print("\nNon Indexed Scans on:")
+    print_non_indexed()
+    print("\nMost Time Consuming:")
+    print(f"{most_time_consuming.name}: {most_time_consuming.time_spent} ms")
+
+    print("\nDetail:\n")
+    for plan in iterate_plans():
+        plan.print()
+
+
+def main():
+    parser = argparse.ArgumentParser(description='Summarize Explain Analyze of 
GNU Taler Database')
+    parser.add_argument('-U', '--user',
+                        dest='db_user',
+                        type=str,
+                        default="",
+                        help='Database user, default current user')
+    parser.add_argument('-P', '--password',
+                        dest='db_pw',
+                        type=str,
+                        default="",
+                        help='Database password, default ""')
+    parser.add_argument('-H', '--host',
+                        dest='db_host',
+                        type=str,
+                        default="",
+                        help='Database host, default unix socket')
+    parser.add_argument('-d', '--database',
+                        dest='db_name',
+                        type=str,
+                        default="",
+                        help='Database name, default current user')
+    parser.add_argument('-p', '--port',
+                        dest='db_port',
+                        type=str,
+                        default="",
+                        help='Database port, default 5432')
+    parser.add_argument(dest='sql',
+                        metavar='sql',
+                        type=str,
+                        help='The query to run and analyze')
+    global args
+    args = parser.parse_args()
+
+    run()
+
+
+if __name__ == '__main__':
+    main()

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.