database/postgres
1
0
Fork 0
mirror of https://github.com/postgres/postgres.git synced 2026-10-23 13:29:09 +03:00
Code Activity
Files
master
postgres/contrib/pg_plan_advice/expected/scan.out
Robert Haas 3311ccc3d2 pg_plan_advice: Handle non-repeatable TABLESAMPLE scans. 
When a tablesample routine says that it is not repeatable across
scans, set_tablesample_rel_pathlist will (usually) materialize it,
confusing pg_plan_advice's plan walker machinery. To fix, update that
machinery to view such Material paths as essentially an extension of
the underlying scan.

Reported-by: Alexander Lakhin <exclusion@gmail.com>
Discussion: http://postgr.es/m/CA+TgmobOOmmXSJz3e+cjTY-bA1+W0dqVDqzxUBEvGtW62whYGg@mail.gmail.com
2026-04-13 08:46:25 -04:00

811 lines
29 KiB
Plaintext
Raw Permalink Blame History

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)
View Git Blame Copy Permalink