sql/pg_hint_plan.sql

   1 CREATE TABLE t1 (val1 int, val2 int);
   2 CREATE TABLE t2 (val1 int, val2 int);
   3 CREATE TABLE t3 (val1 int, val2 int);
   4 CREATE TABLE t4 (val1 int, val2 int);
   5
   6 CREATE VIEW v1 AS SELECT val1, val2 FROM t1;
   7 CREATE VIEW v2 AS SELECT t1.val1 t1_val1, t1.val2 t1_val2, t2.val1 t2_val1, t2.val2 t2_val2 FROM t1, t2 WHERE t1.val1 = t2.val1;
   8 CREATE VIEW v3 AS SELECT t_1.val1 t1_val1, t_1.val2 t1_val2, t_2.val1 t2_val1, t_2.val2 t2_val2 FROM t1 t_1, t2 t_2 WHERE t_1.val1 = t_2.val1;
   9 CREATE VIEW v4 AS SELECT v_2.t1_val1, t_3.val1 FROM v2 v_2, t3 t_3 WHERE v_2.t1_val1 = t_3.val1;
  10
  11 INSERT INTO t1 SELECT i, i FROM (SELECT generate_series(1, 10000) i) t;
  12 INSERT INTO t2 SELECT i, i FROM (SELECT generate_series(1, 1000) i) t;
  13 INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
  14 INSERT INTO t4 SELECT i, i FROM (SELECT generate_series(1, 10) i) t;
  15
  16 CREATE INDEX t1_val1 ON t1 (val1);
  17 CREATE INDEX t2_val1 ON t2 (val1);
  18 CREATE INDEX t3_val1 ON t3 (val1);
  19 CREATE INDEX t4_val1 ON t4 (val1);
  20
  21 ANALYZE t1;
  22 ANALYZE t2;
  23 ANALYZE t3;
  24 ANALYZE t4;
  25
  26 \set t1_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't1'"`
  27 \set t2_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't2'"`
  28 \set t3_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't3'"`
  29 \set t4_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't4'"`
  30
  31 --SET enable_bitmapscan TO off;
  32 --SET enable_hashagg TO off;
  33 --SET enable_tidscan TO off;
  34 --SET enable_sort TO off;
  35 --SET enable_indexscan TO off;
  36 --SET enable_seqscan TO off;
  37 --SET enable_material TO off;
  38 --SET enable_hashjoin TO off;
  39 --SET enable_mergejoin TO off;
  40 --SET enable_nestloop TO off;
  41
  42 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
  43 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
  44
  45 CREATE EXTENSION pg_hint_plan;
  46
  47 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
  48 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
  49
  50 SELECT pg_add_hint('nest(' || :t1_oid || ',' || :t2_oid || ')');
  51 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
  52 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
  53
  54 SELECT pg_add_hint('hash(' || :t1_oid || ',' || :t2_oid || ')');
  55 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
  56 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
  57
  58 SELECT pg_add_hint('merge(' || :t1_oid || ',' || :t2_oid || ')');
  59 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
  60 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
  61
  62 SELECT pg_clear_hint();
  63 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1;
  64 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val2 = t2.val2 AND t2.val2 = t3.val2;
  65
  66 SELECT pg_add_hint('nest(' || :t1_oid || ',' || :t2_oid ||  ',' || :t3_oid || ')');
  67 SELECT pg_add_hint('nest(' || :t1_oid || ',' || :t3_oid || ')');
  68 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1;
  69 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val2 = t2.val2 AND t2.val2 = t3.val2;
  70
  71 SELECT pg_clear_hint();
  72 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  73 SELECT pg_add_hint('no_merge(' || :t1_oid || ',' || :t2_oid || ')');
  74 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  75 SET enable_mergejoin TO off;
  76 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  77 SELECT pg_add_hint('no_hash(' || :t3_oid || ',' || :t4_oid || ')');
  78 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  79 SELECT pg_add_hint('no_nest(' || :t2_oid || ',' || :t3_oid || ',' || :t4_oid || ')');
  80 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  81
  82 SELECT pg_clear_hint();
  83 SET join_collapse_limit TO 10;
  84 EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  85 SET join_collapse_limit TO 1;
  86 EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  87 EXPLAIN SELECT * FROM t2 CROSS JOIN t3 CROSS JOIN t4 CROSS JOIN t1 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
  88
  89 EXPLAIN SELECT * FROM v2;
  90 EXPLAIN SELECT * FROM v3 v_3;
  91 EXPLAIN SELECT * FROM v2 v_2, v3 v_3 WHERE v_2.t1_val1 = v_3.t1_val1;
  92
  93 SELECT pg_enable_log(true);
  94 EXPLAIN SELECT * FROM v4 v_4;
  95 SET from_collapse_limit TO 1;
  96 EXPLAIN SELECT * FROM v4 v_4;
  97 SELECT pg_enable_log(false);