\set PROMPT1 '%[%033[1m%]%M %n@%/:%>%R%[%033[0m%]%# '
\set HISTFILE ~/.psql_history- :PORT
-- This function checks which clauses have extended statistics applied
create or replace function check_clause(text, text) returns table (clause text, extstats text)
language plpgsql as
$$
declare
	sql text;
    ln text;
    tmp text[];
    c text;
    s text;
begin
	if ($1 is not null) then
		sql := format('explain (stats, costs off, %s) %s', $1, $2);
	else
		sql := format('explain (stats, costs off) %s', $2);
	end if;

    for ln in
        execute sql
    loop
        tmp := regexp_match(ln, 'Filter: .*|Group Key: .*');
        if tmp is not null then
            c = tmp[1]::text;
        end if;

        tmp := regexp_match(ln, 'Ext Stats: (.*)');
        if tmp is not null then
            s = tmp[1]::text;
        end if;

        if (c is not null) and (s is not null) then
            return query select c, s;
            c := null;
            s := null;
        end if;
    end loop;
end;
$$;
CREATE FUNCTION
-- prepare
create table t (a int, b int);
CREATE TABLE
insert into t select mod(i, 10), mod(i, 10) from generate_series(1, 100000) s(i);
INSERT 0 100000
-- without extended stats
-- where
EXPLAIN (stats, costs off) select * from t where a = 1 and b = 1;
           QUERY PLAN            
---------------------------------
 Seq Scan on t
   Filter: ((a = 1) AND (b = 1))
(2 rows)

SELECT * FROM check_clause(null, 'select * from t where a = 1 and b = 1');
 clause | extstats 
--------+----------
(0 rows)

-- group by
EXPLAIN (stats, costs off) select 1 from t group by a, b;
     QUERY PLAN      
---------------------
 HashAggregate
   Group Key: a, b
   ->  Seq Scan on t
(3 rows)

SELECT * FROM check_clause(null, 'select * from t group by a, b');
 clause | extstats 
--------+----------
(0 rows)

-- create extended stats
create statistics s on a, b from t;
CREATE STATISTICS
analyze t;
ANALYZE
\dX
                   List of extended statistics
 Schema | Name | Definition  | Ndistinct | Dependencies |   MCV   
--------+------+-------------+-----------+--------------+---------
 public | s    | a, b FROM t | defined   | defined      | defined
(1 row)

-- show extended stats without verbose option
-- where
-- EXPLAIN (stats, costs off) select * from t where a = 1 and b = 1;
SELECT * FROM check_clause(null, 'select * from t where a = 1 and b = 1');
            clause             |             extstats              
-------------------------------+-----------------------------------
 Filter: ((a = 1) AND (b = 1)) | s  Clauses: ((a = 1) AND (b = 1))
(1 row)

-- group by
-- EXPLAIN (stats, costs off) select 1 from t group by a, b;
SELECT * FROM check_clause(null, 'select * from t group by a, b');
     clause      |     extstats     
-----------------+------------------
 Group Key: a, b | s  Clauses: a, b
(1 row)

-- use verbose option
-- where
SELECT * FROM check_clause('verbose', 'select * from t where a = 1 and b = 1');
              clause               |                   extstats                   
-----------------------------------+----------------------------------------------
 Filter: ((t.a = 1) AND (t.b = 1)) | public.s  Clauses: ((t.a = 1) AND (t.b = 1))
(1 row)

-- group by
SELECT * FROM check_clause('verbose', 'select * from t group by a, b');
       clause        |          extstats           
---------------------+-----------------------------
 Group Key: t.a, t.b | public.s  Clauses: t.a, t.b
(1 row)

-- display Ext Stats: twice?!
-- HashAgg
EXPLAIN (stats, costs off) select 1 from t group by a, b;
             QUERY PLAN              
-------------------------------------
 HashAggregate
   Group Key: a, b
   ->  Seq Scan on t
         Ext Stats: s  Clauses: a, b
(4 rows)

SELECT * FROM check_clause(null, 'select * from t group by a, b');
     clause      |     extstats     
-----------------+------------------
 Group Key: a, b | s  Clauses: a, b
(1 row)

-- GroupAgg
set enable_hashagg to off;
SET
EXPLAIN (stats, costs off) select 1 from t group by a, b;
                QUERY PLAN                 
-------------------------------------------
 Group
   Group Key: a, b
   ->  Sort
         Sort Key: a, b
         ->  Seq Scan on t
               Ext Stats: s  Clauses: a, b
(6 rows)

SELECT * FROM check_clause(null, 'select * from t group by a, b');
     clause      |     extstats     
-----------------+------------------
 Group Key: a, b | s  Clauses: a, b
(1 row)

set enable_hashagg to on;
SET
-- Parallel
-- shows twice
set min_parallel_table_scan_size to '0';
SET
EXPLAIN (stats, costs off) select 1 from t group by a, b;
             QUERY PLAN              
-------------------------------------
 HashAggregate
   Group Key: a, b
   ->  Seq Scan on t
         Ext Stats: s  Clauses: a, b
         Ext Stats: s  Clauses: a, b
(5 rows)

SELECT * FROM check_clause(null, 'select * from t group by a, b');
     clause      |     extstats     
-----------------+------------------
 Group Key: a, b | s  Clauses: a, b
(1 row)

set min_parallel_table_scan_size to default;
SET
-- Parallel Hash
-- This is the reason to show ExtStats twice
set enable_parallel_hash to off;
SET
EXPLAIN (stats, costs off) select 1 from t group by a, b;
             QUERY PLAN              
-------------------------------------
 HashAggregate
   Group Key: a, b
   ->  Seq Scan on t
         Ext Stats: s  Clauses: a, b
(4 rows)

SELECT * FROM check_clause(null, 'select * from t group by a, b');
     clause      |     extstats     
-----------------+------------------
 Group Key: a, b | s  Clauses: a, b
(1 row)

set enable_parallel_hash to default;
SET
-- prepare statement: not display Ext Stats because Topmost command is EXECUTE,
-- so isExplain_stats flag seems false
PREPARE ps (INT, INT) AS SELECT * FROM t WHERE a = $1 AND b = $2;
PREPARE
EXPLAIN(stats, costs on, analyze) EXECUTE ps(5, 5);
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1943.00 rows=10033 width=8) (actual time=0.024..13.807 rows=10000 loops=1)
   Filter: ((a = 5) AND (b = 5))
   Rows Removed by Filter: 90000
   Buffers: shared hit=443
 Planning Time: 0.092 ms
 Execution Time: 14.271 ms
(6 rows)

SELECT * FROM check_clause(null, 'EXECUTE ps(5, 5)');
 clause | extstats 
--------+----------
(0 rows)

-- crean up
DROP TABLE t;
DROP TABLE
DROP FUNCTION check_clause(text, text);
DROP FUNCTION
\dX
                 List of extended statistics
 Schema | Name | Definition | Ndistinct | Dependencies | MCV 
--------+------+------------+-----------+--------------+-----
(0 rows)