reproducer_pg16_join_regression.sql
application/octet-stream
Filename: reproducer_pg16_join_regression.sql
Type: application/octet-stream
Part: 0
-- =============================================================================
-- PostgreSQL 16+ Planner Regression Reproducer
-- =============================================================================
--
-- DESCRIPTION:
-- This script creates a synthetic dataset that demonstrates a join ordering
-- regression introduced in PostgreSQL 16. The same query runs in ~70ms on
-- PG 15 but ~1400ms on PG 16/17/18 due to the planner choosing a
-- significantly worse join order.
--
-- TABLES:
-- product_options (~1.39M rows) - master options catalogue
-- pricelist_options (~11.8M rows) - options within price lists
-- option_rules (~628K rows) - rules linking primary→secondary options
-- tax_codes (88 rows) - VAT/tax code lookup
--
-- USAGE:
-- psql -f reproducer_pg16_join_regression.sql
-- (Takes a few minutes to generate ~14M rows)
--
-- EXPECTED BEHAVIOUR:
-- PG 15: Execution time ~70ms (tblaunoptionals0011 scanned 216 times)
-- PG 16+: Execution time ~1400ms (tblaunoptionals0011 scanned 17,325 times)
--
-- The regression is caused by the planner choosing a different join order.
-- Setting join_collapse_limit = 1 restores PG 15 performance on PG 16+.
-- =============================================================================
-- Clean up if re-running
DROP TABLE IF EXISTS pricelist_options CASCADE;
DROP TABLE IF EXISTS option_rules CASCADE;
DROP TABLE IF EXISTS product_options CASCADE;
DROP TABLE IF EXISTS tax_codes CASCADE;
-- =============================================================================
-- 1. SCHEMA DEFINITION
-- =============================================================================
CREATE TABLE tax_codes (
tax_id integer NOT NULL,
description varchar(100),
tax_rate numeric(5,2),
created_at timestamp DEFAULT now(),
created_by_company integer DEFAULT 1,
created_by_user integer DEFAULT 1,
modified_at timestamp DEFAULT now(),
modified_by_user integer DEFAULT 1,
operation_type varchar(10),
row_version bigint DEFAULT 1,
long_description varchar(200),
tax_base numeric(5,2),
flag_1 boolean DEFAULT false,
flag_2 boolean DEFAULT false,
flag_3 boolean DEFAULT false,
flag_4 boolean DEFAULT false,
flag_5 boolean DEFAULT false,
flag_6 boolean DEFAULT false,
flag_7 boolean DEFAULT false,
flag_8 boolean DEFAULT false,
flag_9 boolean DEFAULT false,
CONSTRAINT pk_tax_codes PRIMARY KEY (tax_id)
);
CREATE TABLE product_options (
brand_id integer NOT NULL,
line_code varchar(20) NOT NULL,
model_year varchar(10) NOT NULL,
model_code varchar(20) NOT NULL,
version_code varchar(30) NOT NULL,
option_type varchar(10) NOT NULL,
option_code varchar(20) NOT NULL,
created_at timestamp DEFAULT now(),
created_by_company integer DEFAULT 1,
created_by_user integer DEFAULT 1,
modified_at timestamp DEFAULT now(),
modified_by_user integer DEFAULT 1,
prod_end_date date,
row_version bigint DEFAULT 1,
is_contract boolean DEFAULT false,
note_id integer,
description varchar(200),
CONSTRAINT pk_product_options PRIMARY KEY (brand_id, line_code, model_year, model_code, version_code, option_type, option_code)
);
CREATE TABLE pricelist_options (
pricelist_id integer NOT NULL,
brand_id integer NOT NULL,
line_code varchar(20) NOT NULL,
model_year varchar(10) NOT NULL,
model_code varchar(20) NOT NULL,
version_code varchar(30) NOT NULL,
option_type varchar(10) NOT NULL,
option_code varchar(20) NOT NULL,
price_incl numeric(12,2),
price_excl numeric(12,2),
dealer_net_excl numeric(12,2),
tax_id integer,
modified_at timestamp DEFAULT now(),
created_by_company integer DEFAULT 1,
created_by_user integer DEFAULT 1,
created_at timestamp DEFAULT now(),
modified_by_user integer DEFAULT 1,
row_version bigint DEFAULT 1,
is_visible boolean DEFAULT true,
sort_order integer,
CONSTRAINT pk_pricelist_options PRIMARY KEY (pricelist_id, brand_id, line_code, model_year, model_code, version_code, option_type, option_code)
);
CREATE TABLE option_rules (
brand_id integer NOT NULL,
line_code varchar(20) NOT NULL,
model_year varchar(10) NOT NULL,
model_code varchar(20) NOT NULL,
version_code varchar(30) NOT NULL,
option_type varchar(10) NOT NULL,
option_code varchar(20) NOT NULL,
sec_option_type varchar(10),
constraint_type varchar(10),
created_by_user integer DEFAULT 1,
sec_option_code varchar(20),
created_at timestamp DEFAULT now(),
created_by_company integer DEFAULT 1,
modified_at timestamp DEFAULT now(),
modified_by_user integer DEFAULT 1,
row_version bigint DEFAULT 1,
seq_number integer NOT NULL,
CONSTRAINT pk_option_rules PRIMARY KEY (brand_id, line_code, model_year, model_code, version_code, option_type, option_code, seq_number)
);
-- =============================================================================
-- 2. INDEX CREATION (matches production)
-- =============================================================================
-- Composite index on pricelist_options used for filtered lookups
-- (brand_id, line_code, model_year, model_code, version_code, pricelist_id)
CREATE INDEX ix_pricelist_options_1 ON pricelist_options (
brand_id, line_code, model_year, model_code, version_code, pricelist_id
);
-- =============================================================================
-- 3. DATA GENERATION
-- =============================================================================
-- Key dimensions (derived from production statistics):
-- 18 distinct brand_id values
-- 104 distinct line_code values (not all brands have all lines)
-- 18 distinct model_year values
-- 161 distinct model_code values
-- 5978 distinct version_code values
-- 4 distinct option_type values
-- ~14191 distinct option_code values (~2.4 per version)
-- ~12 pricelists per version
-- 88 tax codes
--
-- Target row counts:
-- product_options: ~1,390,585 (~232 options per version)
-- pricelist_options: ~11,836,440 (~165 pricelist entries per version per pricelist)
-- option_rules: ~627,690 (~105 rules per version)
-- tax_codes: 88
\echo '=== Generating tax_codes (88 rows) ==='
INSERT INTO tax_codes (tax_id, description, tax_rate, operation_type, long_description, tax_base)
SELECT
g AS tax_id,
'Tax code ' || g AS description,
CASE
WHEN g <= 20 THEN round((random() * 25)::numeric, 2)
ELSE 0
END AS tax_rate,
CASE (g % 3)
WHEN 0 THEN 'STD'
WHEN 1 THEN 'RED'
ELSE 'EXM'
END AS operation_type,
'Extended description for tax code ' || g AS long_description,
100.00 AS tax_base
FROM generate_series(1, 88) g;
\echo '=== Generating version dimension (5978 versions) ==='
-- Create a temporary table with all version combinations
-- We use a hierarchical structure: brand → line → model_year → model → version
-- to mimic the correlated nature of the real data
DROP TABLE IF EXISTS _versions;
CREATE TEMPORARY TABLE _versions (
version_id serial PRIMARY KEY,
brand_id integer,
line_code varchar(20),
model_year varchar(10),
model_code varchar(20),
version_code varchar(30)
);
-- Generate 5978 versions with realistic hierarchical distribution
-- ~18 brands, each with varying numbers of lines/models/versions
INSERT INTO _versions (brand_id, line_code, model_year, model_code, version_code)
SELECT
brand_id,
line_code,
model_year,
model_code,
version_code
FROM (
SELECT
((g - 1) % 18) + 1 AS brand_id,
'LN' || lpad((((g - 1) / 18) % 104 + 1)::text, 3, '0') AS line_code,
'MY' || lpad((((g - 1) / (18 * 104)) % 18 + 1)::text, 2, '0') AS model_year,
'MD' || lpad((((g - 1) / (18 * 6)) % 161 + 1)::text, 3, '0') AS model_code,
'VER-' || lpad(g::text, 5, '0') AS version_code,
g
FROM generate_series(1, 5978) g
) sub;
-- Ensure our "target" version exists with known identifiers
-- This is the version that will be queried in the reproducer
-- (maps to brand_id=10, line_code='ABC', model_year='YR',
-- model_code='ABC', version_code='VER-001')
UPDATE _versions
SET brand_id = 10,
line_code = 'ABC',
model_year = 'YR',
model_code = 'ABC',
version_code = 'VER-001'
WHERE version_id = 1;
\echo '=== Generating product_options (~1.39M rows) ==='
\echo ' This may take a minute...'
-- ~232 options per version, using 4 option types with varying option codes
-- Target version (version_id=1) gets exactly 345 options to match production
INSERT INTO product_options (brand_id, line_code, model_year, model_code, version_code,
option_type, option_code, description, note_id)
SELECT
v.brand_id,
v.line_code,
v.model_year,
v.model_code,
v.version_code,
opt_type,
opt_code,
'Option ' || opt_type || '-' || opt_code AS description,
(random() * 100)::integer AS note_id
FROM _versions v
CROSS JOIN LATERAL (
SELECT
'T' || ((g - 1) % 4 + 1) AS opt_type,
'OPT' || lpad(g::text, 5, '0') AS opt_code
FROM generate_series(1,
CASE WHEN v.version_id = 1 THEN 345 ELSE 232 END
) g
) opts(opt_type, opt_code);
\echo '=== Generating option_rules (~628K rows) ==='
\echo ' This may take a minute...'
-- ~105 rules per version, linking primary options to secondary options
-- Each rule points from one option (type+code) to another (sec_type+sec_code)
-- Target version gets exactly 105 rules to match production
INSERT INTO option_rules (brand_id, line_code, model_year, model_code, version_code,
option_type, option_code, sec_option_type, sec_option_code,
constraint_type, seq_number)
SELECT
v.brand_id,
v.line_code,
v.model_year,
v.model_code,
v.version_code,
rule.opt_type,
rule.opt_code,
rule.sec_opt_type,
rule.sec_opt_code,
CASE (g % 3) WHEN 0 THEN 'REQ' WHEN 1 THEN 'EXC' ELSE 'INC' END,
g AS seq_number
FROM _versions v
CROSS JOIN LATERAL (
SELECT
g,
'T' || ((g - 1) % 4 + 1) AS opt_type,
'OPT' || lpad(((g - 1) % 80 + 1)::text, 5, '0') AS opt_code,
-- Secondary option references a different option from the same version
'T' || (((g - 1) + 2) % 4 + 1) AS sec_opt_type,
'OPT' || lpad(((g - 1) % 80 + 81)::text, 5, '0') AS sec_opt_code
FROM generate_series(1,
CASE WHEN v.version_id = 1 THEN 105 ELSE 105 END
) g
) rule;
\echo '=== Generating pricelist_options (~11.8M rows) ==='
\echo ' This will take several minutes...'
-- ~12 pricelists per version, each with ~165 entries
-- Target version + pricelist_id=100 gets exactly 165 rows
-- We assign tax_id from the 88 available tax codes
INSERT INTO pricelist_options (pricelist_id, brand_id, line_code, model_year, model_code,
version_code, option_type, option_code,
price_incl, price_excl, dealer_net_excl,
tax_id, is_visible, sort_order)
SELECT
pl.pricelist_id,
v.brand_id,
v.line_code,
v.model_year,
v.model_code,
v.version_code,
pl.opt_type,
pl.opt_code,
round((random() * 10000)::numeric, 2) AS price_incl,
round((random() * 9000)::numeric, 2) AS price_excl,
round((random() * 7000)::numeric, 2) AS dealer_net_excl,
(g % 88) + 1 AS tax_id,
true AS is_visible,
g AS sort_order
FROM _versions v
CROSS JOIN LATERAL (
SELECT
g,
-- 12 pricelists: for target version, one of them is pricelist 100
CASE
WHEN v.version_id = 1 THEN (g - 1) / 165 + 95 -- pricelists 95..106, includes 100
ELSE (g - 1) / 165 + 1 -- pricelists 1..12
END AS pricelist_id,
'T' || ((g - 1) % 4 + 1) AS opt_type,
'OPT' || lpad(((g - 1) % 165 + 1)::text, 5, '0') AS opt_code
FROM generate_series(1,
CASE WHEN v.version_id = 1 THEN 165 * 12 ELSE 165 * 12 END
) g
) pl;
-- =============================================================================
-- 4. STATISTICS
-- =============================================================================
\echo '=== Running ANALYZE ==='
ANALYZE tax_codes;
ANALYZE product_options;
ANALYZE pricelist_options;
ANALYZE option_rules;
-- =============================================================================
-- 5. VERIFICATION
-- =============================================================================
\echo '=== Row counts ==='
SELECT 'product_options' AS table_name, count(*) AS row_count FROM product_options
UNION ALL SELECT 'pricelist_options', count(*) FROM pricelist_options
UNION ALL SELECT 'option_rules', count(*) FROM option_rules
UNION ALL SELECT 'tax_codes', count(*) FROM tax_codes
ORDER BY table_name;
\echo '=== Target version row counts (should be: 345, 165, 105) ==='
SELECT 'product_options' AS tbl, count(*) AS cnt
FROM product_options
WHERE brand_id = 10 AND line_code = 'ABC' AND model_year = 'YR'
AND model_code = 'ABC' AND version_code = 'VER-001'
UNION ALL
SELECT 'pricelist_options', count(*)
FROM pricelist_options
WHERE brand_id = 10 AND line_code = 'ABC' AND model_year = 'YR'
AND model_code = 'ABC' AND version_code = 'VER-001' AND pricelist_id = 100
UNION ALL
SELECT 'option_rules', count(*)
FROM option_rules
WHERE brand_id = 10 AND line_code = 'ABC' AND model_year = 'YR'
AND model_code = 'ABC' AND version_code = 'VER-001'
ORDER BY tbl;
-- =============================================================================
-- 6. REPRODUCER QUERY
-- =============================================================================
\echo '=== Running reproducer query with EXPLAIN ANALYZE ==='
\echo ' On PG 15: expect ~70ms'
\echo ' On PG 16+: expect ~1400ms'
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
pl.*,
po.*,
tc.*,
rules.*,
pl_check.*,
po2.*
FROM pricelist_options pl
JOIN product_options po
ON po.brand_id = pl.brand_id
AND po.line_code = pl.line_code
AND po.model_year = pl.model_year
AND po.model_code = pl.model_code
AND po.version_code = pl.version_code
AND po.option_type = pl.option_type
AND po.option_code = pl.option_code
LEFT JOIN tax_codes tc
ON tc.tax_id = pl.tax_id
LEFT JOIN option_rules rules
ON rules.brand_id = pl.brand_id
AND rules.line_code = pl.line_code
AND rules.model_year = pl.model_year
AND rules.model_code = pl.model_code
AND rules.version_code = pl.version_code
AND rules.option_type = pl.option_type
AND rules.option_code = pl.option_code
LEFT JOIN pricelist_options pl_check
ON pl_check.pricelist_id = pl.pricelist_id
AND pl_check.brand_id = rules.brand_id
AND pl_check.line_code = rules.line_code
AND pl_check.model_year = rules.model_year
AND pl_check.model_code = rules.model_code
AND pl_check.version_code = rules.version_code
AND pl_check.option_type = rules.sec_option_type
AND pl_check.option_code = rules.sec_option_code
LEFT JOIN product_options po2
ON po2.brand_id = rules.brand_id
AND po2.line_code = rules.line_code
AND po2.model_year = rules.model_year
AND po2.model_code = rules.model_code
AND po2.version_code = rules.version_code
AND po2.option_type = rules.sec_option_type
AND po2.option_code = rules.sec_option_code
WHERE pl.brand_id = 10
AND pl.line_code = 'ABC'
AND pl.model_year = 'YR'
AND pl.model_code = 'ABC'
AND pl.version_code = 'VER-001'
AND pl.pricelist_id = 100;
-- =============================================================================
-- 7. WORKAROUND VERIFICATION
-- =============================================================================
\echo '=== Same query with join_collapse_limit = 1 (workaround) ==='
SET LOCAL join_collapse_limit = 1;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
pl.*,
po.*,
tc.*,
rules.*,
pl_check.*,
po2.*
FROM pricelist_options pl
JOIN product_options po
ON po.brand_id = pl.brand_id
AND po.line_code = pl.line_code
AND po.model_year = pl.model_year
AND po.model_code = pl.model_code
AND po.version_code = pl.version_code
AND po.option_type = pl.option_type
AND po.option_code = pl.option_code
LEFT JOIN tax_codes tc
ON tc.tax_id = pl.tax_id
LEFT JOIN option_rules rules
ON rules.brand_id = pl.brand_id
AND rules.line_code = pl.line_code
AND rules.model_year = pl.model_year
AND rules.model_code = pl.model_code
AND rules.version_code = pl.version_code
AND rules.option_type = pl.option_type
AND rules.option_code = pl.option_code
LEFT JOIN pricelist_options pl_check
ON pl_check.pricelist_id = pl.pricelist_id
AND pl_check.brand_id = rules.brand_id
AND pl_check.line_code = rules.line_code
AND pl_check.model_year = rules.model_year
AND pl_check.model_code = rules.model_code
AND pl_check.version_code = rules.version_code
AND pl_check.option_type = rules.sec_option_type
AND pl_check.option_code = rules.sec_option_code
LEFT JOIN product_options po2
ON po2.brand_id = rules.brand_id
AND po2.line_code = rules.line_code
AND po2.model_year = rules.model_year
AND po2.model_code = rules.model_code
AND po2.version_code = rules.version_code
AND po2.option_type = rules.sec_option_type
AND po2.option_code = rules.sec_option_code
WHERE pl.brand_id = 10
AND pl.line_code = 'ABC'
AND pl.model_year = 'YR'
AND pl.model_code = 'ABC'
AND pl.version_code = 'VER-001'
AND pl.pricelist_id = 100;
RESET join_collapse_limit;
-- =============================================================================
-- 8. CLEANUP (uncomment to drop tables after testing)
-- =============================================================================
-- DROP TABLE IF EXISTS pricelist_options CASCADE;
-- DROP TABLE IF EXISTS option_rules CASCADE;
-- DROP TABLE IF EXISTS product_options CASCADE;
-- DROP TABLE IF EXISTS tax_codes CASCADE;
\echo '=== Done ==='
\echo 'Compare EXPLAIN ANALYZE output from sections 6 and 7.'
\echo 'If section 6 is ~20x slower than section 7, the regression is reproduced.'