7 Contrib Modules

postgres=# LOAD 'auto_explain' ;
LOAD
postgres=# LOAD 'pg_overexplain' ;
LOAD
postgres=# SET auto_explain.log_extension_options = 'DEBUG' ;
SET

postgres=# SET auto_explain.log_analyze = on ;
SET
postgres=# SET auto_explain.log_io = on ;
SET
postgres=# SELECT COUNT(*) FROM data1 ;
  count
----------
 10000000
(1 row)
postgres=# \! tail -6 data/log/postgresql.log
LOG:  duration: 341.638 ms  plan:
        Query Text: SELECT COUNT(*) FROM data1;
        Aggregate  (cost=179055.00..179055.01 rows=1 width=8) (actual time=…
          ->  Seq Scan on data1  (cost=0.00..154055.00 rows=10000000 width=…
                Prefetch: avg=46.37 max=91 capacity=94
                I/O: count=2397 waits=11 size=15.89 in-progress=4.27

postgres=# \! cat /tmp/multi1.csv
HEADER1-1,HEADER1-2
HEADER2-1,HEADER2-2
100,data1
200,data2
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER filesvr1 FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE multi1(col1 INT, col2 VARCHAR(10)) SERVER
filesvr1 OPTIONS (filename '/tmp/multi1.csv', HEADER '2', format 'csv') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM multi1 ;
 col1 | col2
------+-------
  100 | data1
  200 | data2
(2 rows)

postgres=# SELECT * FROM pg_buffercache_mark_dirty_all() ;
-[ RECORD 1 ]---------+------
buffers_dirtied       | 5527
buffers_already_dirty | 10857
buffers_skipped       | 0

postgres=# LOAD 'pg_plan_advice' ;
LOAD
postgres=# EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM data1 AS d1
INNER JOIN data2 AS d2 ON d1.col1 = d2.col1 WHERE d1.col1 < 1000 ;
                       QUERY PLAN
---------------------------------------------------------
 Nested Loop
   ->  Index Scan using data1_pkey on data1 d1
         Index Cond: (col1 < 1000)
   ->  Index Scan using data2_pkey on data2 d2
         Index Cond: (col1 = d1.col1)
 Generated Plan Advice:
   JOIN_ORDER(d1 d2)
   NESTED_LOOP_PLAIN(d2)
   INDEX_SCAN(d1 public.data1_pkey d2 public.data2_pkey)
   NO_GATHER(d1 d2)
(10 rows)

postgres=# SET pg_plan_advice.advice = 'JOIN_ORDER(d2 d1)' ;
SET
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM data1 AS d1 INNER JOIN data2 AS
d2 ON d1.col1 = d2.col1 WHERE d1.col1 < 1000 ;
                        QUERY PLAN
-----------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Hash Join
         Hash Cond: (d2.col1 = d1.col1)
         ->  Parallel Seq Scan on data2 d2
         ->  Hash
               ->  Index Scan using data1_pkey on data1 d1
                     Index Cond: (col1 < 1000)
 Supplied Plan Advice:
   JOIN_ORDER(d2 d1) /* matched */
(10 rows)

Specify the changes to the execution plan in the pg_plan_advice.advice parameter. The major settings
are listed below.

postgres=# SHOW shared_preload_libraries ;
 shared_preload_libraries
--------------------------
 pg_stash_advice
(1 row)
postgres=# SHOW compute_query_id ;
 compute_query_id
------------------
 on
(1 row)
postgres=# CREATE EXTENSION pg_stash_advice ;
CREATE EXTENSION

Create the advice stash. Specify the name for the pg_create_advice_stash function, and set the name
of the advice just created to the pg_advice_stash.stash_name parameter.

postgres=# SELECT pg_create_advice_stash('stash1') ;
pg_create_advice_stash
------------------------
(1 row)
postgres=# SET pg_stash_advice.stash_name = 'stash1' ;
SET

Identify the query ID and execution plan of the query for which the execution plan needs to be
changed.

postgres=# EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM data1 AS d1 INNER
 JOIN data2 AS d2 ON d1.col1 = d2.col1 WHERE d1.col1 = 10000 ;
                      QUERY PLAN
------------------------------------------------------
 Nested Loop
   Output: d1.col1, d1.col2, d2.col1, d2.col2
   ->  Index Scan using data1_pkey on public.data1 d1
         Output: d1.col1, d1.col2
         Index Cond: (d1.col1 = 10000)
   ->  Index Scan using data2_pkey on public.data2 d2
         Output: d2.col1, d2.col2
         Index Cond: (d2.col1 = 10000)
 Query Identifier: 1524396781679940627
(9 rows)

Specify the advice to change the execution plan for the query ID. In the following example, the join
order is changed. For instructions on changing the execution plan, refer to the pg_plan_advice Contrib
module.

postgres=#  SELECT pg_set_stashed_advice('stash1', 1524396781679940627,
'JOIN_ORDER(d2 d1)') ;
 pg_set_stashed_advice
-----------------------
(1 row)

Check the execution plan. The "Supplied Plan Advice" entry is output, indicating that advice for
changing the execution plan has been specified.

postgres=# EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM data1 AS d1 INNER JOIN
data2 AS d2 ON d1.col1 = d2.col1 WHERE d1.col1 = 10000 ;
                      QUERY PLAN
------------------------------------------------------
 Nested Loop
   Output: d1.col1, d1.col2, d2.col1, d2.col2
   ->  Index Scan using data2_pkey on public.data2 d2
         Output: d2.col1, d2.col2
         Index Cond: (d2.col1 = 10000)
   ->  Index Scan using data1_pkey on public.data1 d1
         Output: d1.col1, d1.col2
         Index Cond: (d1.col1 = 10000)
 Query Identifier: 1524396781679940627
 Supplied Plan Advice:
   JOIN_ORDER(d2 d1) /* matched */
(11 rows)

postgres=> SELECT query, generic_plan_calls, custom_plan_calls FROM
pg_stat_statements WHERE query LIKE 'PREPARE%' ;
-[ RECORD 1 ]------+-----------------------------------
query              | PREPARE prepare1(int) AS SELECT $1
generic_plan_calls | 4
custom_plan_calls  | 4

postgres=> BEGIN ;
BEGIN
postgres=*> DECLARE c1 CURSOR FOR SELECT * FROM data1 ;
DECLARE CURSOR
postgres=*> FETCH 1 FROM c1 ;
 c1 |  c2
----+-------
  1 | data1
(1 row)
postgres=*> FETCH 2 FROM c1 ;
 c1 |  c2
----+-------
  2 | data2
  3 | data3
(2 rows)
postgres=*> SELECT query, calls FROM pg_stat_statements WHERE query LIKE
'FETCH%' ;
      query       | calls
------------------+-------
 FETCH $1 FROM c1 |     2
(1 row)

postgres=> CREATE FOREIGN TABLE data1(col1 INT, col2 VARCHAR(10)) SERVER dbsvr1
OPTIONS (restore_stats 'true') ;
CREATE FOREIGN TABLE
postgres=> ANALYZE (VERBOSE) data1 ;
INFO:  importing statistics for foreign table "public.data1"
INFO:  finished importing statistics for foreign table "public.data1"
ANALYZE

postgres=> SET plan_cache_mode = force_generic_plan ;
SET
postgres=> PREPARE array_stmt(VARCHAR[]) AS SELECT * FROM data1 WHERE
col2 = ANY($1) ;
PREPARE
postgres=> EXPLAIN (VERBOSE, COSTS OFF) EXECUTE
array_stmt(ARRAY['data1', 'data2']) ;

postgres=> EXPLAIN (VERBOSE, COSTS OFF) EXECUTE
array_stmt(ARRAY['data1', 'data2']) ;
                     QUERY PLAN
-----------------------------------------------------
 Foreign Scan on public.data1
   Output: col1, col2
   Filter: ((data1.col2)::text = ANY (($1)::text[]))
   Remote SQL: SELECT col1, col2 FROM public.data1
(4 rows)

postgres=> EXPLAIN (VERBOSE, COSTS OFF) EXECUTE
array_stmt(ARRAY['data1', 'data2']) ;
                                            QUERY PLAN
----------------------------------------------------------------------------…
 Foreign Scan on public.data1
   Output: col1, col2
   Remote SQL: SELECT col1, col2 FROM public.data1 WHERE ((col2 = ANY
($1::character varying[])))
(3 rows)