/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
contrib/pg_plan_advice/expected/scan.out
809 строк
29 KB
Robert Haas
pg_plan_advice: Handle non-repeatable TABLESAMPLE scans.
13 апр 2026, 15:46
13 апр 2026, 15:46
3311ccc
Код
Авторство
О чём код?
LOAD 'pg_plan_advice'; SET max_parallel_workers_per_gather = 0; SET seq_page_cost = 0.1; SET random_page_cost = 0.1; SET cpu_tuple_cost = 0; SET cpu_index_tuple_cost = 0; CREATE TABLE scan_table (a int primary key, b text) WITH (autovacuum_enabled = false); INSERT INTO scan_table SELECT g, 'some text ' || g FROM generate_series(1, 100000) g; CREATE INDEX scan_table_b ON scan_table USING brin (b); VACUUM ANALYZE scan_table; -- Sequential scan EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table; QUERY PLAN ------------------------- Seq Scan on scan_table Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (4 rows) -- Index scan EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (5 rows) -- Index-only scan EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------ Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (5 rows) -- Bitmap heap scan EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE b > 'some text 8'; QUERY PLAN ----------------------------------------------- Bitmap Heap Scan on scan_table Recheck Cond: (b > 'some text 8'::text) -> Bitmap Index Scan on scan_table_b Index Cond: (b > 'some text 8'::text) Generated Plan Advice: BITMAP_HEAP_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) -- TID scan EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid = '(0,1)'; QUERY PLAN ----------------------------------- Tid Scan on scan_table TID Cond: (ctid = '(0,1)'::tid) Generated Plan Advice: TID_SCAN(scan_table) NO_GATHER(scan_table) (5 rows) -- TID range scan EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid > '(1,1)' AND ctid < '(2,1)'; QUERY PLAN --------------------------------------------------------------- Tid Range Scan on scan_table TID Cond: ((ctid > '(1,1)'::tid) AND (ctid < '(2,1)'::tid)) Generated Plan Advice: TID_SCAN(scan_table) NO_GATHER(scan_table) (5 rows) -- Try forcing each of our test queries to use the scan type they -- wanted to use anyway. This should succeed. BEGIN; SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table; QUERY PLAN -------------------------------------- Seq Scan on scan_table Supplied Plan Advice: SEQ_SCAN(scan_table) /* matched */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (6 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN -------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------------- Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_ONLY_SCAN(scan_table scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE b > 'some text 8'; QUERY PLAN ----------------------------------------------- Bitmap Heap Scan on scan_table Recheck Cond: (b > 'some text 8'::text) -> Bitmap Index Scan on scan_table_b Index Cond: (b > 'some text 8'::text) Supplied Plan Advice: BITMAP_HEAP_SCAN(scan_table) /* matched */ Generated Plan Advice: BITMAP_HEAP_SCAN(scan_table) NO_GATHER(scan_table) (9 rows) SET LOCAL pg_plan_advice.advice = 'TID_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid = '(0,1)'; QUERY PLAN -------------------------------------- Tid Scan on scan_table TID Cond: (ctid = '(0,1)'::tid) Supplied Plan Advice: TID_SCAN(scan_table) /* matched */ Generated Plan Advice: TID_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid > '(1,1)' AND ctid < '(2,1)'; QUERY PLAN --------------------------------------------------------------- Tid Range Scan on scan_table TID Cond: ((ctid > '(1,1)'::tid) AND (ctid < '(2,1)'::tid)) Supplied Plan Advice: TID_SCAN(scan_table) /* matched */ Generated Plan Advice: TID_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) COMMIT; -- Try to force a full scan of the table to use some other scan type. All -- of these will fail. An index scan or bitmap heap scan could potentially -- generate the correct answer, but the planner does not even consider these -- possibilities due to the lack of a WHERE clause. BEGIN; SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table; QUERY PLAN ---------------------------------------------------------------- Seq Scan on scan_table Disabled: true Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_pkey) /* matched, failed */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table; QUERY PLAN --------------------------------------------------------------------- Seq Scan on scan_table Disabled: true Supplied Plan Advice: INDEX_ONLY_SCAN(scan_table scan_table_pkey) /* matched, failed */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table; QUERY PLAN ------------------------------------------------------ Seq Scan on scan_table Disabled: true Supplied Plan Advice: BITMAP_HEAP_SCAN(scan_table) /* matched, failed */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'TID_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table; QUERY PLAN ---------------------------------------------- Seq Scan on scan_table Disabled: true Supplied Plan Advice: TID_SCAN(scan_table) /* matched, failed */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) COMMIT; -- Try again to force index use. This should now succeed for the INDEX_SCAN -- and BITMAP_HEAP_SCAN, but the INDEX_ONLY_SCAN can't be forced because the -- query fetches columns not included in the index. BEGIN; SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a > 0; QUERY PLAN -------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a > 0) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a > 0; QUERY PLAN --------------------------------------------------------------------- Seq Scan on scan_table Disabled: true Filter: (a > 0) Supplied Plan Advice: INDEX_ONLY_SCAN(scan_table scan_table_pkey) /* matched, failed */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (8 rows) SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a > 0; QUERY PLAN ---------------------------------------------- Bitmap Heap Scan on scan_table Recheck Cond: (a > 0) -> Bitmap Index Scan on scan_table_pkey Index Cond: (a > 0) Supplied Plan Advice: BITMAP_HEAP_SCAN(scan_table) /* matched */ Generated Plan Advice: BITMAP_HEAP_SCAN(scan_table) NO_GATHER(scan_table) (9 rows) COMMIT; -- We can force a primary key lookup to use a sequential scan, but we -- can't force it to use an index-only scan (due to the column list) -- or a TID scan (due to the absence of a TID qual). If we apply DO_NOT_SCAN -- here, we should get a valid plan anyway, but with the scan disabled. BEGIN; SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN -------------------------------------- Seq Scan on scan_table Filter: (a = 1) Supplied Plan Advice: SEQ_SCAN(scan_table) /* matched */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN --------------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Disabled: true Index Cond: (a = 1) Supplied Plan Advice: INDEX_ONLY_SCAN(scan_table scan_table_pkey) /* matched, failed */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) SET LOCAL pg_plan_advice.advice = 'TID_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------- Index Scan using scan_table_pkey on scan_table Disabled: true Index Cond: (a = 1) Supplied Plan Advice: TID_SCAN(scan_table) /* matched, failed */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------- Index Scan using scan_table_pkey on scan_table Disabled: true Index Cond: (a = 1) Supplied Plan Advice: DO_NOT_SCAN(scan_table) /* matched, failed */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) COMMIT; -- We can forcibly downgrade an index-only scan to an index scan, but we can't -- force the use of an index that the planner thinks is inapplicable. BEGIN; SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN -------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table public.scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN --------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_b)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Disabled: true Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_b) /* matched, failed */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) COMMIT; -- We can force the use of a sequential scan in place of a bitmap heap scan, -- but a plain index scan on a BRIN index is not possible. BEGIN; SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE b > 'some text 8'; QUERY PLAN -------------------------------------- Seq Scan on scan_table Filter: (b > 'some text 8'::text) Supplied Plan Advice: SEQ_SCAN(scan_table) /* matched */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_b)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Disabled: true Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_b) /* matched, failed */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) COMMIT; -- We can force the use of a sequential scan rather than a TID scan or -- TID range scan. BEGIN; SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid = '(0,1)'; QUERY PLAN -------------------------------------- Seq Scan on scan_table Filter: (ctid = '(0,1)'::tid) Supplied Plan Advice: SEQ_SCAN(scan_table) /* matched */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid > '(1,1)' AND ctid < '(2,1)'; QUERY PLAN ------------------------------------------------------------- Seq Scan on scan_table Filter: ((ctid > '(1,1)'::tid) AND (ctid < '(2,1)'::tid)) Supplied Plan Advice: SEQ_SCAN(scan_table) /* matched */ Generated Plan Advice: SEQ_SCAN(scan_table) NO_GATHER(scan_table) (7 rows) COMMIT; -- Test more complex scenarios with index scans. BEGIN; -- Should still work if we mention the schema. SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table public.scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN --------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) -- But not if we mention the wrong schema. SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table cilbup.scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table cilbup.scan_table_pkey) /* matched, inapplicable, failed */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) -- It's OK to repeat the same advice. SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey scan_table scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN -------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_pkey) /* matched */ INDEX_SCAN(scan_table scan_table_pkey) /* matched */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) -- But it doesn't work if the index target is even notionally different. SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey scan_table public.scan_table_pkey)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1; QUERY PLAN ---------------------------------------------------------------------------- Index Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table scan_table_pkey) /* matched, conflicting */ INDEX_SCAN(scan_table public.scan_table_pkey) /* matched, conflicting */ Generated Plan Advice: INDEX_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (8 rows) COMMIT; -- Test assorted incorrect advice. BEGIN; SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(nothing)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------ Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: SEQ_SCAN(nothing) /* not matched */ Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(nothing whatsoever)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------ Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(nothing whatsoever) /* not matched */ Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table bogus)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN -------------------------------------------------------------------- Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_SCAN(scan_table bogus) /* matched, inapplicable, failed */ Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(nothing whatsoever)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN --------------------------------------------------------- Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_ONLY_SCAN(nothing whatsoever) /* not matched */ Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table bogus)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1; QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using scan_table_pkey on scan_table Index Cond: (a = 1) Supplied Plan Advice: INDEX_ONLY_SCAN(scan_table bogus) /* matched, inapplicable, failed */ Generated Plan Advice: INDEX_ONLY_SCAN(scan_table public.scan_table_pkey) NO_GATHER(scan_table) (7 rows) COMMIT; -- Test our ability to refer to multiple instances of the same alias. BEGIN; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x LEFT JOIN scan_table s ON g = s.a; QUERY PLAN ------------------------------------------------------------------- Nested Loop Left Join -> Nested Loop Left Join -> Function Scan on generate_series g -> Index Scan using scan_table_pkey on scan_table s Index Cond: (a = g.g) -> Index Scan using scan_table_pkey on scan_table s_1 Index Cond: (a = g.g) Generated Plan Advice: JOIN_ORDER(g s s#2) NESTED_LOOP_PLAIN(s s#2) INDEX_SCAN(s public.scan_table_pkey s#2 public.scan_table_pkey) NO_GATHER(g s s#2) (12 rows) SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x LEFT JOIN scan_table s ON g = s.a; QUERY PLAN ---------------------------------------------------------- Nested Loop Left Join -> Hash Left Join Hash Cond: (g.g = s.a) -> Function Scan on generate_series g -> Hash -> Seq Scan on scan_table s -> Index Scan using scan_table_pkey on scan_table s_1 Index Cond: (a = g.g) Supplied Plan Advice: SEQ_SCAN(s) /* matched */ Generated Plan Advice: JOIN_ORDER(g s s#2) NESTED_LOOP_PLAIN(s#2) HASH_JOIN(s) SEQ_SCAN(s) INDEX_SCAN(s#2 public.scan_table_pkey) NO_GATHER(g s s#2) (17 rows) SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s#2)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x LEFT JOIN scan_table s ON g = s.a; QUERY PLAN -------------------------------------------------------------- Hash Left Join Hash Cond: (g.g = s_1.a) -> Nested Loop Left Join -> Function Scan on generate_series g -> Index Scan using scan_table_pkey on scan_table s Index Cond: (a = g.g) -> Hash -> Seq Scan on scan_table s_1 Supplied Plan Advice: SEQ_SCAN(s#2) /* matched */ Generated Plan Advice: JOIN_ORDER(g s s#2) NESTED_LOOP_PLAIN(s) HASH_JOIN(s#2) SEQ_SCAN(s#2) INDEX_SCAN(s public.scan_table_pkey) NO_GATHER(g s s#2) (17 rows) SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s) SEQ_SCAN(s#2)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x LEFT JOIN scan_table s ON g = s.a; QUERY PLAN ------------------------------------------------ Hash Left Join Hash Cond: (g.g = s_1.a) -> Hash Left Join Hash Cond: (g.g = s.a) -> Function Scan on generate_series g -> Hash -> Seq Scan on scan_table s -> Hash -> Seq Scan on scan_table s_1 Supplied Plan Advice: SEQ_SCAN(s) /* matched */ SEQ_SCAN(s#2) /* matched */ Generated Plan Advice: JOIN_ORDER(g s s#2) HASH_JOIN(s s#2) SEQ_SCAN(s s#2) NO_GATHER(g s s#2) (17 rows) COMMIT; -- Test our ability to refer to scans within a subquery. EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x; QUERY PLAN -------------------------------------------------- Index Scan using scan_table_pkey on scan_table s Index Cond: (a = 1) Generated Plan Advice: INDEX_SCAN(s@x public.scan_table_pkey) NO_GATHER(x s@x) (5 rows) EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0); QUERY PLAN --------------------------------------------------------- Index Scan using scan_table_pkey on scan_table s Index Cond: (a = 1) Generated Plan Advice: INDEX_SCAN(s@unnamed_subquery public.scan_table_pkey) NO_GATHER(unnamed_subquery s@unnamed_subquery) (5 rows) BEGIN; -- Should not match. SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x; QUERY PLAN -------------------------------------------------- Index Scan using scan_table_pkey on scan_table s Index Cond: (a = 1) Supplied Plan Advice: SEQ_SCAN(s) /* not matched */ Generated Plan Advice: INDEX_SCAN(s@x public.scan_table_pkey) NO_GATHER(x s@x) (7 rows) EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0); QUERY PLAN --------------------------------------------------------- Index Scan using scan_table_pkey on scan_table s Index Cond: (a = 1) Supplied Plan Advice: SEQ_SCAN(s) /* not matched */ Generated Plan Advice: INDEX_SCAN(s@unnamed_subquery public.scan_table_pkey) NO_GATHER(unnamed_subquery s@unnamed_subquery) (7 rows) -- Should match first query only. SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s@x)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x; QUERY PLAN ------------------------------- Seq Scan on scan_table s Filter: (a = 1) Supplied Plan Advice: SEQ_SCAN(s@x) /* matched */ Generated Plan Advice: SEQ_SCAN(s@x) NO_GATHER(x s@x) (7 rows) EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0); QUERY PLAN --------------------------------------------------------- Index Scan using scan_table_pkey on scan_table s Index Cond: (a = 1) Supplied Plan Advice: SEQ_SCAN(s@x) /* not matched */ Generated Plan Advice: INDEX_SCAN(s@unnamed_subquery public.scan_table_pkey) NO_GATHER(unnamed_subquery s@unnamed_subquery) (7 rows) -- Should match second query only. SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s@unnamed_subquery)'; EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x; QUERY PLAN -------------------------------------------------- Index Scan using scan_table_pkey on scan_table s Index Cond: (a = 1) Supplied Plan Advice: SEQ_SCAN(s@unnamed_subquery) /* not matched */ Generated Plan Advice: INDEX_SCAN(s@x public.scan_table_pkey) NO_GATHER(x s@x) (7 rows) EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0); QUERY PLAN -------------------------------------------------- Seq Scan on scan_table s Filter: (a = 1) Supplied Plan Advice: SEQ_SCAN(s@unnamed_subquery) /* matched */ Generated Plan Advice: SEQ_SCAN(s@unnamed_subquery) NO_GATHER(unnamed_subquery s@unnamed_subquery) (7 rows) COMMIT; -- Test a non-repeatable tablesample method with a scan-level Materialize. CREATE EXTENSION tsm_system_time; CREATE TABLE scan_tsm (i int); EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT 1 FROM (SELECT i FROM scan_tsm TABLESAMPLE system_time (1000)), LATERAL (SELECT i LIMIT 1); QUERY PLAN ---------------------------------------------------------------------- Nested Loop -> Materialize -> Sample Scan on scan_tsm Sampling: system_time ('1000'::double precision) -> Limit -> Result Generated Plan Advice: JOIN_ORDER(scan_tsm unnamed_subquery#2) NESTED_LOOP_PLAIN(unnamed_subquery#2) NO_GATHER(unnamed_subquery#2 scan_tsm "*RESULT*"@unnamed_subquery) (10 rows) -- Same, but with the scan-level Materialize on the inner side of a join. EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT 1 FROM (SELECT 1 AS x LIMIT 1), LATERAL (SELECT x FROM scan_tsm TABLESAMPLE system_time (1000)); QUERY PLAN -------------------------------------------------------------------- Nested Loop -> Limit -> Result -> Materialize -> Sample Scan on scan_tsm Sampling: system_time ('1000'::double precision) Generated Plan Advice: JOIN_ORDER(unnamed_subquery scan_tsm) NESTED_LOOP_PLAIN(scan_tsm) NO_GATHER(unnamed_subquery scan_tsm "*RESULT*"@unnamed_subquery) (10 rows)