LOAD 'pg_hint_plan'; ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan'; SET pg_hint_plan.enable_hint TO on; SET pg_hint_plan.debug_print TO on; SET client_min_messages TO LOG; CREATE TABLE s1.tl (a int); INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a); -- Queries on ordinary tables with default setting EXPLAIN (COSTS false) SELECT * FROM s1.t1; QUERY PLAN ---------------- Seq Scan on t1 (1 row) SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; SET max_parallel_workers_per_gather to DEFAULT; /*+Parallel(t1 8)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1; LOG: pg_hint_plan: used hint: Parallel(t1 8 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------- Gather Workers Planned: 2 -> Parallel Seq Scan on t1 (3 rows) /*+Parallel(t1 8 soft)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1; LOG: pg_hint_plan: used hint: Parallel(t1 8 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------- Gather Workers Planned: 2 -> Parallel Seq Scan on t1 (3 rows) /*+Parallel(t1 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1; LOG: pg_hint_plan: used hint: Parallel(t1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------- Gather Workers Planned: 8 -> Parallel Seq Scan on t1 (3 rows) -- Queries on inheritance tables SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; /*+Parallel(p1 8)*/ EXPLAIN (COSTS false) SELECT * FROM p1; LOG: pg_hint_plan: used hint: Parallel(p1 8 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------- Gather Workers Planned: 1 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 (12 rows) SET parallel_setup_cost to DEFAULT; SET parallel_tuple_cost to DEFAULT; SET min_parallel_table_scan_size to DEFAULT; SET min_parallel_index_scan_size to DEFAULT; /*+Parallel(p1 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1; LOG: pg_hint_plan: used hint: Parallel(p1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------- Gather Workers Planned: 8 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 (12 rows) -- hinting on children makes the whole inheritance parallel /*+Parallel(p1_c1 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1; LOG: pg_hint_plan: used hint: Parallel(p1_c1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------- Gather Workers Planned: 8 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 (12 rows) -- Joins EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id; QUERY PLAN ------------------------------------------ Hash Join Hash Cond: (p1_c1_c1.id = p2_c1_c1.id) -> Seq Scan on p1_c1_c1 -> Hash -> Seq Scan on p2_c1_c1 (5 rows) /*+Parallel(p1_c1_c1 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id; LOG: pg_hint_plan: used hint: Parallel(p1_c1_c1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Hash Join Hash Cond: (p2_c1_c1.id = p1_c1_c1.id) -> Seq Scan on p2_c1_c1 -> Hash -> Gather Workers Planned: 8 -> Parallel Seq Scan on p1_c1_c1 (7 rows) SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; /*+Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0)*/ EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id; LOG: pg_hint_plan: used hint: Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------ Gather Workers Planned: 1 -> Hash Join Hash Cond: (p1_c1_c1.id = p2_c1_c1.id) -> Parallel Seq Scan on p1_c1_c1 -> Hash -> Seq Scan on p2_c1_c1 (7 rows) /*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0)*/ EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id; LOG: pg_hint_plan: used hint: Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------ Gather Workers Planned: 8 -> Hash Join Hash Cond: (p1_c1_c1.id = p2_c1_c1.id) -> Parallel Seq Scan on p1_c1_c1 -> Hash -> Seq Scan on p2_c1_c1 (7 rows) /*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id; LOG: pg_hint_plan: used hint: Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Hash Join Hash Cond: (p1_c1_c1.id = p2_c1_c1.id) -> Gather Workers Planned: 8 -> Parallel Seq Scan on p1_c1_c1 -> Hash -> Gather Workers Planned: 8 -> Parallel Seq Scan on p2_c1_c1 (9 rows) -- Joins on inheritance tables SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; /*+Parallel(p1 8)*/ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: Parallel(p1 8 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Gather Workers Planned: 1 -> Hash Join Hash Cond: (p1.id = p2.id) -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Hash -> Append -> Seq Scan on p2 -> Seq Scan on p2_c1 -> Seq Scan on p2_c2 -> Seq Scan on p2_c3 -> Seq Scan on p2_c4 -> Seq Scan on p2_c1_c1 -> Seq Scan on p2_c1_c2 -> Seq Scan on p2_c3_c1 -> Seq Scan on p2_c3_c2 (25 rows) /*+Parallel(p1 8)Parallel(p2 0)*/ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: Parallel(p1 8 soft) Parallel(p2 0 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Gather Workers Planned: 1 -> Hash Join Hash Cond: (p1.id = p2.id) -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Hash -> Append -> Seq Scan on p2 -> Seq Scan on p2_c1 -> Seq Scan on p2_c2 -> Seq Scan on p2_c3 -> Seq Scan on p2_c4 -> Seq Scan on p2_c1_c1 -> Seq Scan on p2_c1_c2 -> Seq Scan on p2_c3_c1 -> Seq Scan on p2_c3_c2 (25 rows) SET parallel_setup_cost to DEFAULT; SET parallel_tuple_cost to DEFAULT; SET min_parallel_table_scan_size to DEFAULT; SET min_parallel_index_scan_size to DEFAULT; /*+Parallel(p2 8 soft)*/ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: Parallel(p2 8 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Gather Workers Planned: 1 -> Hash Join Hash Cond: (p2.id = p1.id) -> Append -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 -> Hash -> Append -> Seq Scan on p1 -> Seq Scan on p1_c1 -> Seq Scan on p1_c2 -> Seq Scan on p1_c3 -> Seq Scan on p1_c4 -> Seq Scan on p1_c1_c1 -> Seq Scan on p1_c1_c2 -> Seq Scan on p1_c3_c1 -> Seq Scan on p1_c3_c2 (25 rows) /*+Parallel(p2 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: Parallel(p2 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Gather Workers Planned: 8 -> Hash Join Hash Cond: (p2.id = p1.id) -> Append -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 -> Hash -> Append -> Seq Scan on p1 -> Seq Scan on p1_c1 -> Seq Scan on p1_c2 -> Seq Scan on p1_c3 -> Seq Scan on p1_c4 -> Seq Scan on p1_c1_c1 -> Seq Scan on p1_c1_c2 -> Seq Scan on p1_c3_c1 -> Seq Scan on p1_c3_c2 (25 rows) /*+Parallel(p2 8 hard) Parallel(p1 5 hard) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: Parallel(p1 5 hard) Parallel(p2 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------------- Hash Join Hash Cond: (p1.id = p2.id) -> Gather Workers Planned: 5 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Hash -> Gather Workers Planned: 8 -> Append -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 (27 rows) -- Mixture with a scan hint -- p1 can be parallel /*+Parallel(p1 8 hard) IndexScan(p2) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: IndexScan(p2) Parallel(p1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN -------------------------------------------------------------- Hash Join Hash Cond: (p2.id = p1.id) -> Append -> Index Scan using p2_id2_val on p2 -> Index Scan using p2_c1_id2_val on p2_c1 -> Index Scan using p2_c2_id2_val on p2_c2 -> Index Scan using p2_c3_id_val_idx on p2_c3 -> Index Scan using p2_c4_id_val_idx on p2_c4 -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 -> Hash -> Gather Workers Planned: 8 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 (25 rows) -- Parallel sequential scan /*+Parallel(p1 8 hard) SeqScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: SeqScan(p1) Parallel(p1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------- Gather Workers Planned: 8 -> Hash Join Hash Cond: (p1.id = p2.id) -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Hash -> Append -> Seq Scan on p2 -> Seq Scan on p2_c1 -> Seq Scan on p2_c2 -> Seq Scan on p2_c3 -> Seq Scan on p2_c4 -> Seq Scan on p2_c1_c1 -> Seq Scan on p2_c1_c2 -> Seq Scan on p2_c3_c1 -> Seq Scan on p2_c3_c2 (25 rows) -- Parallel index scan /*+Parallel(p1 8 hard) IndexScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: IndexScan(p1) Parallel(p1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ----------------------------------------------------------------------- Gather Workers Planned: 8 -> Hash Join Hash Cond: (p1.id = p2.id) -> Append -> Parallel Index Scan using p1_pkey on p1 -> Parallel Index Scan using p1_c1_pkey on p1_c1 -> Parallel Index Scan using p1_c2_pkey on p1_c2 -> Parallel Index Scan using p1_c3_pkey on p1_c3 -> Parallel Index Scan using p1_c4_pkey on p1_c4 -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1 -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2 -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1 -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2 -> Hash -> Append -> Seq Scan on p2 -> Seq Scan on p2_c1 -> Seq Scan on p2_c2 -> Seq Scan on p2_c3 -> Seq Scan on p2_c4 -> Seq Scan on p2_c1_c1 -> Seq Scan on p2_c1_c2 -> Seq Scan on p2_c3_c1 -> Seq Scan on p2_c3_c2 (25 rows) -- This hint doesn't turn on parallel, so the Parallel hint is ignored set max_parallel_workers_per_gather TO 0; /*+Parallel(p1 0 hard) IndexScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: used hint: IndexScan(p1) not used hint: Parallel(p1 0 hard) duplication hint: error hint: QUERY PLAN -------------------------------------------------------- Hash Join Hash Cond: (p1.id = p2.id) -> Append -> Index Scan using p1_pkey on p1 -> Index Scan using p1_c1_pkey on p1_c1 -> Index Scan using p1_c2_pkey on p1_c2 -> Index Scan using p1_c3_pkey on p1_c3 -> Index Scan using p1_c4_pkey on p1_c4 -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 -> Hash -> Append -> Seq Scan on p2 -> Seq Scan on p2_c1 -> Seq Scan on p2_c2 -> Seq Scan on p2_c3 -> Seq Scan on p2_c4 -> Seq Scan on p2_c1_c1 -> Seq Scan on p2_c1_c2 -> Seq Scan on p2_c3_c1 -> Seq Scan on p2_c3_c2 (23 rows) -- Parallel on UNION EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; QUERY PLAN ---------------------------- Append -> Seq Scan on p1 -> Seq Scan on p1_c1 -> Seq Scan on p1_c2 -> Seq Scan on p1_c3 -> Seq Scan on p1_c4 -> Seq Scan on p1_c1_c1 -> Seq Scan on p1_c1_c2 -> Seq Scan on p1_c3_c1 -> Seq Scan on p1_c3_c2 -> Seq Scan on p2 -> Seq Scan on p2_c1 -> Seq Scan on p2_c2 -> Seq Scan on p2_c3 -> Seq Scan on p2_c4 -> Seq Scan on p2_c1_c1 -> Seq Scan on p2_c1_c2 -> Seq Scan on p2_c3_c1 -> Seq Scan on p2_c3_c2 (19 rows) -- parallel hinting on any relation enables parallel SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; SET max_parallel_workers_per_gather to 0; /*+Parallel(p1 8) */ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; LOG: pg_hint_plan: used hint: Parallel(p1 8 soft) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------- Gather Workers Planned: 1 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 (21 rows) -- set hint does the same thing /*+Set(max_parallel_workers_per_gather 1)*/ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; LOG: pg_hint_plan: used hint: Set(max_parallel_workers_per_gather 1) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------- Gather Workers Planned: 1 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 (21 rows) -- applies largest number of workers on merged parallel paths SET parallel_setup_cost to DEFAULT; SET parallel_tuple_cost to DEFAULT; SET min_parallel_table_scan_size to DEFAULT; SET min_parallel_index_scan_size to DEFAULT; SET max_parallel_workers_per_gather to 8; /*+Parallel(p1 5 hard)Parallel(p2 6 hard) */ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; LOG: pg_hint_plan: used hint: Parallel(p1 5 hard) Parallel(p2 6 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------- Gather Workers Planned: 6 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 (21 rows) -- num of workers of non-hinted relations should be default value SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; SET max_parallel_workers_per_gather to 3; SET enable_indexscan to false; /*+Parallel(p1 8 hard) */ EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id; LOG: pg_hint_plan: used hint: Parallel(p1 8 hard) not used hint: duplication hint: error hint: QUERY PLAN ------------------------------------------------------- Hash Join Hash Cond: (t1.id = p1.id) -> Gather Workers Planned: 3 -> Parallel Seq Scan on t1 -> Hash -> Gather Workers Planned: 8 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 (18 rows) -- Negative hint SET enable_indexscan to DEFAULT; SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_table_scan_size to 0; SET min_parallel_index_scan_size to 0; SET max_parallel_workers_per_gather to 5; EXPLAIN (COSTS false) SELECT * FROM p1; QUERY PLAN ------------------------------------------- Gather Workers Planned: 1 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 (12 rows) /*+Parallel(p1 0 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1; LOG: pg_hint_plan: used hint: Parallel(p1 0 hard) not used hint: duplication hint: error hint: QUERY PLAN ---------------------------- Append -> Seq Scan on p1 -> Seq Scan on p1_c1 -> Seq Scan on p1_c2 -> Seq Scan on p1_c3 -> Seq Scan on p1_c4 -> Seq Scan on p1_c1_c1 -> Seq Scan on p1_c1_c2 -> Seq Scan on p1_c3_c1 -> Seq Scan on p1_c3_c2 (10 rows) -- Errors /*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard) Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; INFO: pg_hint_plan: hint syntax error at or near "100x" DETAIL: number of workers must be a number: Parallel INFO: pg_hint_plan: hint syntax error at or near "-1000" DETAIL: number of workers must be positive: Parallel INFO: pg_hint_plan: hint syntax error at or near "1000000" DETAIL: number of workers = 1000000 is larger than max_worker_processes(8): Parallel INFO: pg_hint_plan: hint syntax error at or near "hoge" DETAIL: enforcement must be soft or hard: Parallel INFO: pg_hint_plan: hint syntax error at or near ")" DETAIL: wrong number of arguments (1): Parallel INFO: pg_hint_plan: hint syntax error at or near ")" DETAIL: wrong number of arguments (4): Parallel LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: Parallel(p1 100x hard) Parallel(p1 -1000 hard) Parallel(p1 1000000 hard) Parallel(p1 8 soft) Parallel() Parallel() QUERY PLAN ------------------------------------------- Gather Workers Planned: 1 -> Append -> Parallel Seq Scan on p1 -> Parallel Seq Scan on p1_c1 -> Parallel Seq Scan on p1_c2 -> Parallel Seq Scan on p1_c3 -> Parallel Seq Scan on p1_c4 -> Parallel Seq Scan on p1_c1_c1 -> Parallel Seq Scan on p1_c1_c2 -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Parallel Seq Scan on p2 -> Parallel Seq Scan on p2_c1 -> Parallel Seq Scan on p2_c2 -> Parallel Seq Scan on p2_c3 -> Parallel Seq Scan on p2_c4 -> Parallel Seq Scan on p2_c1_c1 -> Parallel Seq Scan on p2_c1_c2 -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 (21 rows) -- Hints on unhintable relations are just ignored /*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1) TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */ EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10) UNION ALL SELECT id FROM ft1 UNION ALL (WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1) UNION ALL SELECT userid FROM pg_stat_statements fs1 UNION ALL SELECT x FROM (VALUES (1), (2), (3)) t(x); LOG: pg_hint_plan: used hint: Parallel(p1 5 hard) not used hint: IndexScan(*VALUES*) SeqScan(cte1) TidScan(fs1) IndexScan(ft1) IndexScan(t) Parallel(s1 3 hard) duplication hint: error hint: QUERY PLAN ----------------------------------------------------------------------------------------------- Append -> Result -> Append -> Sample Scan on p1_c1_c1 s1 Sampling: system ('10'::real) -> Foreign Scan on ft1 Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv -> CTE Scan on cte1 CTE cte1 -> Gather Workers Planned: 5 -> Append -> Parallel Seq Scan on p1 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c1 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c2 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c3 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c4 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c1_c1 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c1_c2 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c3_c1 Filter: ((id % 2) = 0) -> Parallel Seq Scan on p1_c3_c2 Filter: ((id % 2) = 0) -> Function Scan on pg_stat_statements -> Subquery Scan on "*SELECT* 5" -> Values Scan on "*VALUES*" (33 rows) ALTER SYSTEM SET session_preload_libraries TO DEFAULT; SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row)