A strange message is emitted after a crash or the first time

[pgstoreplans/pg_store_plans.git] / makeplanfile.sql

diff --git a/makeplanfile.sql b/makeplanfile.sql
index f56c88f..72f6e88 100644 (file)
--- a/makeplanfile.sql
+++ b/makeplanfile.sql
@@ -5,13 +5,19 @@ set work_mem = '1MB';
 drop table if exists tt1;
 drop table if exists tt2;
 drop table if exists tt3;
-create table tt1 (a int, b int not null, c text);
-create table tt2 (a int, b int, c text);
-create table tt3 (a int, b int, c text);
+drop table if exists p cascade;
+drop table if exists ct1;
+create table p (a int, b int, c text);
+create table tt1 (a int, b int not null, c text) inherits (p);
+create table tt2 (a int, b int, c text) inherits (p);
+create table tt3 (a int, b int, c text) inherits (p);
 create index i_tt1 on tt1(a);
 create index i_tt2 on tt2(a);
 create index i_tt3_a on tt3(a);
 create index i_tt3_b on tt3(b);
+create table ct1 (a int unique, b int);
+insert into ct1 values (1,1), (2,2);
+
 create or replace function t_tt1_1() returns trigger as $$
   BEGIN
     NEW.b := -NEW.a;
@@ -56,7 +62,7 @@ explain (analyze on, buffers on, verbose on, format :format)
 \echo ###### Index scan (forward) ANY, array in expr, escape
 explain (analyze on, buffers on, verbose on, format :format)
    select * from tt1 "x""y" where a in (50, 120, 300, 500);
-\echo ###### Index scan (backward), MergeJoin, Sort, quichsort, alias
+\echo ###### Index scan (backward), MergeJoin, Sort, quicksort, alias
 explain (analyze on, buffers on, verbose on, format :format)
    select x.b, x.c  from tt1 x join tt2 y on (x.a = -y.b * 3)
    order by x.a desc limit 10;
@@ -90,7 +96,7 @@ set work_mem = '128kB';
 explain (analyze on, buffers on, verbose on, format :format)
    select b from tt1 group by b;
 set work_mem = '1MB';
-\echo ###### SetOp intersect, SbuqueryScan
+\echo ###### SetOp intersect, SubqueryScan
 explain (analyze on, buffers on, verbose on, format :format)
    select a from tt1 intersect select b from tt2 order by a;
 \echo ###### Sorted SetOp, Sort on Disk
@@ -158,5 +164,94 @@ rollback;
 \echo ###### Materialize
 explain (analyze on, buffers on, verbose on, format :format)
    select * from tt1 where a = all(select b from tt2);
+\echo ###### Update on partitioned tables
+explain (analyze on, buffers on, verbose on, format :format)
+   UPDATE p SET b = b + 1;
+\echo ###### Delete on partitioned tables
+explain (analyze on, buffers on, verbose on, format :format)
+   DELETE FROM p WHERE a = 100;
+\echo ###### ON CONFLICT
+explain (analyze on, buffers on, verbose on, format :format)
+   INSERT INTO ct1 VALUES (1,1) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b + 1;
+\echo ###### GROUP BY
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT a, b, max(c) FROM tt1 GROUP BY a, b;
+\echo ###### GROUPING SETS
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT a, b, max(c) FROM tt1 GROUP BY GROUPING SETS ((a), (b), ());
+\echo ###### Table sample
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT * FROM tt1 TABLESAMPLE system(1) REPEATABLE (1);
+
+\echo ###### Project Set
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT * from XMLTABLE('//towns/town'
+    PASSING BY REF '<towns><town><name>Toronto</name></town><town><name>Ottawa</name></town></towns>'
+        COLUMNS name text);
+
+\echo ###### Incremental Sort
+explain (analyze on, buffers on, verbose on, format :format)
+       WITH x AS (SELECT i/100 + 1 AS a, i + 1 AS b FROM generate_series(0, 999) i)
+         SELECT * FROM (SELECT * FROM x ORDER BY a) s ORDER BY a, b LIMIT 31;
+
+-- Named Tuplestore Scan -- requires auto_explain
+DROP TABLE IF EXISTS e1 CASCADE;
+CREATE TABLE e1 (a int, b int);
+CREATE OR REPLACE function e1_t1() RETURNS TRIGGER AS $$
+DECLARE
+  total int;
+BEGIN
+  SELECT sum(a) INTO total FROM post;
+  NEW.b := total;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER e1_t1 AFTER INSERT ON e1
+ REFERENCING NEW TABLE AS post
+ FOR EACH ROW EXECUTE PROCEDURE e1_t1();
+
+INSERT INTO e1 VALUES (1, 1);
+
+load 'auto_explain';
+set auto_explain.log_min_duration to 0;
+set auto_explain.log_analyze to true;
+set auto_explain.log_buffers to true;
+set auto_explain.log_buffers to true;
+set auto_explain.log_format to :format;
+set auto_explain.log_timing  to true;
+set auto_explain.log_nested_statements to true;
+set client_min_messages to LOG;
+set log_min_messages to FATAL; -- Inhibit LOG by auto_explain
+\echo ###### Named Tuplestore Scan
+CREATE TRIGGER e1_t2 AFTER UPDATE ON e1
+ REFERENCING NEW TABLE AS post OLD TABLE AS pre
+ FOR EACH ROW EXECUTE PROCEDURE e1_t1();
+UPDATE e1 SET a = a + 1;
+set client_min_messages to DEFAULT;
+set log_min_messages to DEFAULT;
+set auto_explain.log_min_duration to -1;
+
+-- ###### Parallel
+drop table if exists lt1;
+create table lt1 (a int, b text);
+alter table lt1 alter column b set storage plain;
+insert into lt1 (select a, repeat('x', 1000) from generate_series(0, 99999) a);
+set max_parallel_workers_per_gather to 2;
+set parallel_tuple_cost to 0;
+set parallel_setup_cost to 0;
+set min_parallel_table_scan_size to 0;
+set min_parallel_index_scan_size to 0;
+
+\echo ###### Parallel Seq Scan
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT * FROM lt1;
+
+\echo ###### Parallel Index Scan
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT * FROM tt1 where a < 100;
+
+\echo ###### Gather Merge
+explain (analyze on, buffers on, verbose on, format :format)
+   SELECT a FROM tt1 ORDER BY a;
 
 -- BitmapAnd/Inner/Right/ForegnScan