mirror of
https://github.com/postgres/postgres.git
synced 2026-10-23 13:29:09 +03:00
7b7c4a8dcc
Simple EXISTS subplans can be converted to hashed ANY subplans as an alternative implementation. Previously, both the original EXISTS subplan and the converted ANY subplan used names with the exists_ prefix, making EXPLAIN output harder to read and pg_plan_advice targets less clear. Use the exists_to_any_ prefix for converted ANY subplans so that their names distinguish them from the original EXISTS alternative. This only changes the names shown by EXPLAIN and used by plan advice; it does not affect planner behavior. Author: Yugo Nagata <nagata@sraoss.co.jp> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: solai v <solai.cdac@gmail.com> Reviewed-by: Fujii Masao <masao.fujii@gmail.com> Discussion: https://postgr.es/m/20260605165641.3950f99ace0aad8f807abe96@sraoss.co.jp Backpatch-through: 19
59 lines
2.3 KiB
PL/PgSQL
59 lines
2.3 KiB
PL/PgSQL
LOAD 'pg_plan_advice';
|
|
SET max_parallel_workers_per_gather = 0;
|
|
|
|
CREATE TABLE alt_t1 (a int) WITH (autovacuum_enabled = false);
|
|
CREATE TABLE alt_t2 (a int) WITH (autovacuum_enabled = false);
|
|
CREATE INDEX ON alt_t2(a);
|
|
INSERT INTO alt_t1 SELECT generate_series(1, 1000);
|
|
INSERT INTO alt_t2 SELECT generate_series(1, 100000);
|
|
VACUUM ANALYZE alt_t1;
|
|
VACUUM ANALYZE alt_t2;
|
|
|
|
-- This query uses an OR to prevent the EXISTS from being converted to a
|
|
-- semi-join, forcing the planner through the AlternativeSubPlan path.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM alt_t1
|
|
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
|
|
|
|
-- We should be able to force either AlternativeSubPlan by advising against
|
|
-- scanning the other relation.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_1)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM alt_t1
|
|
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
|
|
SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_t2@exists_to_any_1)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM alt_t1
|
|
WHERE EXISTS (SELECT 1 FROM alt_t2 WHERE alt_t2.a = alt_t1.a) OR alt_t1.a < 0;
|
|
COMMIT;
|
|
|
|
-- Now let's test a case involving MinMaxAggPath, which we treat similarly
|
|
-- to the AlternativeSubPlan case.
|
|
CREATE TABLE alt_minmax (a int) WITH (autovacuum_enabled = false);
|
|
CREATE INDEX ON alt_minmax(a);
|
|
INSERT INTO alt_minmax SELECT generate_series(1, 10000);
|
|
VACUUM ANALYZE alt_minmax;
|
|
|
|
-- Using an Index Scan inside of an InitPlan should win over a full table
|
|
-- scan.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT min(a), max(a) FROM alt_minmax;
|
|
|
|
-- Advising against the scan of alt_minmax at the root query level should
|
|
-- change nothing, but if we say we don't want either of or both of the
|
|
-- minmax-variant scans, the plan should switch to a full table scan.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_minmax)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT min(a), max(a) FROM alt_minmax;
|
|
SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_minmax@minmax_1)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT min(a), max(a) FROM alt_minmax;
|
|
SET LOCAL pg_plan_advice.advice = 'DO_NOT_SCAN(alt_minmax@minmax_1) DO_NOT_SCAN(alt_minmax@minmax_2)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT min(a), max(a) FROM alt_minmax;
|
|
COMMIT;
|
|
|
|
DROP TABLE alt_t1, alt_t2, alt_minmax;
|