pg_sqltxplain - Simplifying PostgreSQL Execution Plan Analysis.

Analyzing execution plans is made easier with curating execution plan, statistics of database objects such as tables, indexes, or columns involved in the actual runtime execution plan, all within a single report. This makes it easier to share among team members or external forum and reduces the need for additional information requests.

This tool automates the curation of object statistics when analyzing problematic execution plans in PostgreSQL using an HTML template embded in sql file and thepsqlcommand line.

How it Works?

Execution plan is generated either byExplain Analyze Buffersor only withExplainand stored in a plantable.

Using thepsqlcommand line, the current statistics of all database objects involved in Execution plan are fetched and included in the HTML output. The output can also be integrated with thePEV2 visualiser.

Installation

Setup Databases with necessary wrappers code that generate execution plan and stored it in plantable.

Extension Dependency

This tool uses thepg_stat_statementsview to extract runtime information of problematic SQL usingqueryid.It can also be used as filters to gather Generic Plan frompg_stat_statements.Thepgstattupleextension is used to extract bloat-related information, though it is optional.

Name | Description
--------------------+------------------------------------------------------------------------
pg_stat_statements | track planning and execution statistics of all SQL statements executed
pgstattuple | show tuple-level statistics

Creating planstats schema

Gathering statistics requires traversing all execution plan steps and extracting the objects involved. We have built all necessary wrappers with functions and views within theplanstatsschema. You need to set it up on the concerned databases.

PGPASSWORD=********psql -h<<PostgresHost>>-U <<SuperUser>> -d <<Databases>> -f pg_sqltxplan/initialsetup.sql

Generating Report including underlying stats on objects and execution plan.

We have multiple options to generate report either directly from pg_stat_statements using GENERIC_PLAN supported since PostgreSQL 16 or run SQL within wrapper functions(run_plan_analyze/run_plan_explain)

Option 1 - Running Problematic SQL usingrun_plan_analyzeWrapper

Using Dollar Quoting enclosed problematic SQL as input and run it using functionrun_plan_analyzedefined inplanstatsschema.

plantest=#select planstats.run_plan_analyze($$select count(1) from emp$$);
run_plan_analyze
-------------------------
(1,7335632667878063635)
(1row)

It will return internal planid and queryid for further references.

In next steps, we will generate pg_sqltxplain report usingpsqlcommand line.If no Filter is provided by default it will generate report on last plan analyzed(max-planid).

PGPASSWORD=*********psql -h<<PostgresHost>>-U <<PGuser>> -d <<Databases>> -q -v ON_ERROR_STOP=1 -v query_id=7335632667878063635 -f pg_sqltxplain.sql
Gathering Database Object Stats for Query ID(7335632667878063635)
Underlying Statistics curated for Query(7335632667878063635) - Output File Stats_Via_Explain_Analyze_7335632667878063635.html

Please note - Replace Host, DBname and Password as per your DB instances.

Option 2 - Running Only Explain on Problematic SQL using Wrapper

Using Dollar Quoting enclosed problematic SQL as input and run it using functionrun_plan_explaindefined inplanstatsschema.

plantest=#select planstats.run_plan_explain($$select count(1) from emp$$);
run_plan_analyze
-------------------------
(1,7335632667878063635)
(1row)

In next steps, we will generate pg_sqltxplain report usingpsqlcommand line.If no Filter is provided by default it will generate report on last plan analyzed(max-planid).

PGPASSWORD=*********psql -h<<PostgresHost>>-U <<PGuser>> -d <<Databases>> -q -v ON_ERROR_STOP=1 -v query_id=7335632667878063635 -f pg_sqltxplain.sql
Gathering Database Object Stats for Query ID(7335632667878063635)
Underlying Statistics curated for Query(7335632667878063635) - Output File pg_sqltxplain_7335632667878063635.html

Option 3 - Running usingpg_stat_statementsperformance views (Preferably for PostgreSQL 16 onwards)

Using -v option ofpsql,we can passqueryidfilters along withpg_stat_statementsto use internal performance views to extract query metadata. It internally usedGENERIC_PLANplan options to generate underlying explain plan usingquerycolumn.

PGPASSWORD=*********psql -h<<PostgresHost>>-U <<PGuser>> -d <<Databases>> -q -v ON_ERROR_STOP=1 -v query_id=8192079375982646892 -v pg_stat_statements= -f pg_sqltxplain.sql

Integrations withPev2 Visualiser

Integrate Execution plan objects statistics withPEV2 visualisera graphical vizualization of a PostgreSQL execution plan.

With any of the options mentioned previously, we can choose to get underlying stats of Objects with auto integrated it with PEV2. Internally it use two sql file to generate couple of html report as we are using iframe html tag to take care of different stylesheet.

Please note that we will need to share both generated HTML files.

PGPASSWORD=*********psql -h<<PostgresHost>>-U <<PGuser>> -d <<Databases>> -q -v ON_ERROR_STOP=1 -f explain_dalibo.sql -f pg_sqltxplain_with_dalibo.sql

Sample Report

Check out sample html report created using pg_sqltxplan utility.

1. Sample 1 - pg_sqltxplain
2. Sample 2 - pg_sqltxplain
3. Sample 3 - pg_sqltxplainDalibo

Contact Details.

Feel free to mail us([email protected]) for any issues or consulting on PostgreSQL performance Tuning.

www.datacloudgaze.com