DiffStats and ExplainFull (DSEF)
=================================

*Detailed SQL reports for third party help & support*

.

DiffStats and ExplainFull can generate detailed reports which are useful for 
troubleshooting performance of a SQL statement, and especially for working 
with third parties who are helping in the process. It reduces the amount of
back-and-forth requests for information by capturing a great deal of commonly
useful data about the performance of a SQL statement.

The report is textual and can be reviewed before passing it along to a third
party. For all tables involved in the query, DSEF will look at every column
in the table, and for any column which has them, the report will include the
beginning and end of histograms and MCVs. This typically includes the lowest
and highest literal values in those columns. Query text and explain plan output
can also contain literal data values. These are all used by the query planner,
and they are needed - for example - in order to determine if out-of-range
statistics [1] are impacting planning. Nevertheless, in some cases it may be
necessary to manually redact literals before passing along the report to a
third party.

The extension consists of a number of functions which are installed into the
database. These functions fall into two broad categories:

1. A function that is a wrapper around "EXPLAIN ANALYZE" - besides ensuring 
   that all diagnostics options are used, it also dumps additional information
   like server version and full planner statistics for all functions and tables 
   referenced by the SQL.
2. A set of functions to capture and report all possible statistics tracked by 
   the database during a test SQL statement execution


[1]: for more information about out-of-range statistics, see
https://pganalyze.com/blog/5mins-postgres-out-of-range-planner-statistics

Installation
-------------

The quickest way to install DSEF is to create its functions with this command:

    curl -O https://raw.githubusercontent.com/ardentperf/dsef/main/sql/dsef.sql
    psql <dsef.sql

The SQL file at the URL above can also be opened and executed with a GUI development 
tool like pgAdmin.

Alternatively, DSEF can be installed by an administrator as an **extension**. This 
requires a few more steps but has some advantages. The functions are clearly identified 
as part of a single package, the functions are not mixed up with user code in pg_dump
backups, and the package versioning and metadata is tracked in system catalogs and
much easier to manage. Installation as an extension is preferred, when possible.

Install extension to self-managed PostgreSQL from source:

    git clone https://github.com/ardentperf/dsef
    
    make
    make installcheck
    make install

Install extension to self-managed PostgreSQL with PGXN

    apt install pgxnclient

    pgxn install dsef

Install extension to RDS 14.5+ (including Aurora)

    aws rds modify-db-parameter-group
       --db-parameter-group-name custom-param-group-name
       --parameters "ParameterName=shared_preload_libraries,ParameterValue=pg_tle"    
    CREATE EXTENSION pg_tle;

    curl -O https://raw.githubusercontent.com/ardentperf/dsef/main/tle/dsef.tle
    psql <dsef.tle


Un-Installation
----------------

If DSEF is installed as an extension, then uninstalling it is very simple:

    DROP EXTENSION dsef

If DSEF was installed as functions, you can carefully copy-and-paste the `DROP FUNCTION` 
commands from https://github.com/ardentperf/dsef/blob/main/sql/uninstall_dsef.sql


Dependencies
-------------

To use the "ExplainFull" functionality, the only required privilege is the 
ability to create a function.

At present, the "DiffStats" functionality requires privileges to create a temporary 
table to pass information to subsequent function calls. (Core PostgreSQL does not 
yet provide session-level variables.) As a result, the "DiffStats" functionality will 
not work on a hot standby or otherwise read-only PostgreSQL system.

Optional dependencies:
* If pg_proctab is available, DSEF will use it to include all available 
  Operating System statistics in its report.
* On Aurora, DSEF will include wait event statistics at both the system and 
  session level in its report.
* With appropriate privileges, DSEF can be installed as an extension on 
  self-hosted PostgreSQL and on RDS PosgreSQL 14.5+


A Few Simple Examples (Tutorial)
---------------------------------

First step: use the `explain_analyze_full()` function instead of "explain analyze". It 
will run EXPLAIN ANALYZE under the covers and will ensure that the full output is always 
printed. This is especially helpful when working with other people on understanding a 
query, because it reduces the number of times people need to ask for more information.

    select * from explain_analyze_full('select * from sbtest1 limit 1');

PostgreSQL has a special kind of quoting called "dollar quoting" which is very useful 
if we need to use single-quotes in our query text.

    select * from explain_analyze_full($$ select sum(k) from sbtest1 where c>'97string0' $$); 

To get even more information (including wait times on Aurora), we wrap our explain analyze 
inside `ds_start()` and `ds_report()`. This will take a snapshot of system counters and 
produce a report about which counters changed during query execution. Note that as of 2024, 
most open source PostgreSQL counters operate at the database level rather than the session 
level. These can be very useful on a test system that's otherwise idle, besides the debug 
session running this command. On a busier system, it can still be helpful to have an idea
what the overall system profile is during execution. Aurora wait events are captured and 
reported at both the system and the session level.

There is sometimes a short delay in PostgreSQL after query completion and before stat updates 
become visible, so it helps to include a small `pg_sleep()` before getting the report if 
you are copy/pasting everything at once rather than keying by hand.

NOTE: the `ds_start()` function will attempt to automatically install and use the 
`pg_proctab` extension, if it is available and the user has privileges. If this causes an 
error, then the error is ignored and `pg_proctab` statistics are not included in the report.

    select ds_start();

      select * from explain_analyze_full('select * from customers c,customers2 c2  where c.id=c2.id limit 1000000');

    select pg_sleep(0.05); select * from ds_report();

More than 10 years ago, Tom Kyte published a short snippet of code called RunStats. Simple 
yet brilliant - it allowed a SQL statement to be executed twice, compared stats between the 
two executions, and printed a report. We can do the same thing on PostgreSQL by inserting 
a call to `ds_capture()` between our two SQL statements.

    select ds_start(); 

      select * from explain_analyze_full($$ select * from customers c,customers2 c2  where c.id=c2.id limit 1000 $$); 

    select * from ds_capture(); 

      set enable_indexscan=off; 
      select * from explain_analyze_full($$ select * from customers c,customers2 c2  where c.id=c2.id limit 1000 $$); 

    select pg_sleep(0.05); select * from ds_report_diff();

In PostgreSQL, we need to be careful about running DML inside of EXPLAIN ANALYZE because 
the SQL is actually executed to gather stats. This means that we typically want to wrap the 
command inside a transaction, and then ROLLBACK.

    select ds_start(); 

      BEGIN; 
      select * from explain_analyze_full($$ update my_test set t=99 where i=100000 $$); 
      ROLLBACK; 

    select pg_sleep(0.05); select * from ds_report();