-- The cost model's constants as planner settings (DESIGN.md §31, "The -- settings"). -- -- Every per-operation price of lion's cost model is a setting, -- pg_lion._cost, holding the multiplier of the core cost setting the -- price is written in, so that the model can be calibrated on a workload as -- core's is with random_page_cost. The defaults are the values the prices -- had as constants, so every other test plans exactly as before. This one -- lists them, shows that four of them - an index scan's, a count's, the -- FK-side join's and a collected range's - are read by the planner, and that -- a SET LOCAL of one ends with its transaction. \set VERBOSITY terse SET client_min_messages = warning; LOAD 'pg_lion'; CREATE EXTENSION IF NOT EXISTS pg_lion; RESET client_min_messages; -- VACUUM can only set all-visible once the commit record is on disk SET synchronous_commit = on; -- a sample of every row: the statistics, and the plans, do not depend on it SET default_statistics_target = 1000; SET max_parallel_workers_per_gather = 0; -- ---------- 1. the settings ---------- -- user settings, real-valued, each at the value its constant had SELECT name, setting, boot_val, context, vartype FROM pg_settings WHERE name LIKE 'pg_lion.%\_cost' ORDER BY name; name | setting | boot_val | context | vartype ---------------------------------------+---------+----------+---------+--------- pg_lion.and_member_cost | 0.8 | 0.8 | user | real pg_lion.bitmap_member_cost | 0.2 | 0.2 | user | real pg_lion.bitmap_row_cost | 0.1 | 0.1 | user | real pg_lion.bitmap_word_cost | 0.07 | 0.07 | user | real pg_lion.cold_page_cost | 50 | 50 | user | real pg_lion.cold_stream_page_cost | 6.25 | 6.25 | user | real pg_lion.container_cost | 8 | 8 | user | real pg_lion.descent_cost | 120 | 120 | user | real pg_lion.distinct_test_cost | 50 | 50 | user | real pg_lion.entry_count_cost | 50 | 50 | user | real pg_lion.fkjoin_batch_row_cost | 75 | 75 | user | real pg_lion.fkjoin_collect_container_cost | 2 | 2 | user | real pg_lion.fkjoin_copy_container_cost | 20 | 20 | user | real pg_lion.fkjoin_copy_count_cost | 33 | 33 | user | real pg_lion.fkjoin_copy_member_cost | 3 | 3 | user | real pg_lion.fkjoin_copy_probe_cost | 2 | 2 | user | real pg_lion.fkjoin_count_cost | 14 | 14 | user | real pg_lion.fkjoin_lookup_cost | 75 | 75 | user | real pg_lion.fkjoin_probe_cost | 10 | 10 | user | real pg_lion.fkjoin_probe_page_cost | 54 | 54 | user | real pg_lion.fkjoin_row_cost | 20 | 20 | user | real pg_lion.fkjoin_set_cost | 11 | 11 | user | real pg_lion.fkjoin_sort_compare_cost | 0.25 | 0.25 | user | real pg_lion.fkjoin_sort_key_cost | 6 | 6 | user | real pg_lion.fkjoin_sort_random_page_cost | 0.25 | 0.25 | user | real pg_lion.fkjoin_sort_seq_page_cost | 0.75 | 0.75 | user | real pg_lion.list_group_cost | 18 | 18 | user | real pg_lion.member_cost | 0.15 | 0.15 | user | real pg_lion.memory_probe_cost | 30 | 30 | user | real pg_lion.plain_fetch_row_cost | 1 | 1 | user | real pg_lion.position_check_cost | 4.3 | 4.3 | user | real pg_lion.position_member_cost | 2.4 | 2.4 | user | real pg_lion.probe_cost | 40 | 40 | user | real pg_lion.probe_entry_cost | 4.5 | 4.5 | user | real pg_lion.probe_rescan_cost | 16 | 16 | user | real pg_lion.probe_step_cost | 10 | 10 | user | real pg_lion.range_entry_cost | 40 | 40 | user | real pg_lion.range_fold_cost | 420 | 420 | user | real pg_lion.range_union_entry_cost | 12 | 12 | user | real pg_lion.recheck_group_tid_cost | 2 | 2 | user | real pg_lion.recheck_sum_tid_cost | 6 | 6 | user | real pg_lion.recheck_tid_cost | 1.5 | 1.5 | user | real pg_lion.resident_page_cost | 120 | 120 | user | real pg_lion.slice_container_cost | 15 | 15 | user | real pg_lion.slice_decode_bit_cost | 0.3 | 0.3 | user | real pg_lion.slice_decode_row_cost | 1 | 1 | user | real pg_lion.slice_gather_cost | 12 | 12 | user | real pg_lion.slice_key_cost | 120 | 120 | user | real pg_lion.slice_page_cost | 200 | 200 | user | real pg_lion.slice_range_container_cost | 900 | 900 | user | real pg_lion.slice_range_key_cost | 700 | 700 | user | real pg_lion.slice_range_step_cost | 8 | 8 | user | real pg_lion.slice_range_word_cost | 0.1 | 0.1 | user | real pg_lion.slice_step_cost | 1000 | 1000 | user | real pg_lion.slice_step_key_cost | 50 | 50 | user | real pg_lion.slice_step_row_cost | 0.9 | 0.9 | user | real pg_lion.spill_read_cost | 27 | 27 | user | real pg_lion.union_key_cost | 30 | 30 | user | real pg_lion.union_member_cost | 0.25 | 0.25 | user | real pg_lion.union_set_cost | 100 | 100 | user | real pg_lion.walk_pass_cost | 5 | 5 | user | real (61 rows) -- from 0 up, among the customized options as every extension's settings are SELECT count(*) AS settings, bool_and(min_val::float8 = 0) AS from_zero, bool_and(max_val::float8 > 1e300) AS unbounded, bool_and(category = 'Customized Options') AS custom_group, bool_and(short_desc ~ ', in multiples of (cpu_operator_cost|cpu_tuple_cost|seq_page_cost|random_page_cost)\.$') AS unit_named FROM pg_settings WHERE name LIKE 'pg_lion.%\_cost'; settings | from_zero | unbounded | custom_group | unit_named ----------+-----------+-----------+--------------+------------ 61 | t | t | t | t (1 row) -- 50000 rows; a has 10 values and g 100, both placed at random, about 55 -- rows to a page CREATE TABLE lcg (id int, a int, g int, pad text); INSERT INTO lcg SELECT i, (hashint4(i) & 2147483647) % 10, (hashint4(i + 1000000) & 2147483647) % 100, repeat('x', 100) FROM generate_series(1, 50000) i; CREATE INDEX lcg_a ON lcg USING lion (a); CREATE INDEX lcg_g ON lcg USING lion (g); VACUUM (FREEZE, ANALYZE) lcg; -- the FK-side join: a fact of 40000 rows over 2000 keys, 20 rows a key, and -- its dimension, 100 rows to a region CREATE TABLE lcf (id int, fk int8, x int); INSERT INTO lcf SELECT i, (i * 7919) % 2000 + 1, i % 10 FROM generate_series(1, 40000) i; CREATE INDEX lcf_fk ON lcf USING lion (fk); CREATE INDEX lcf_x ON lcf USING lion (x); CREATE TABLE lcd (pk int8 PRIMARY KEY, attr int, region text); INSERT INTO lcd SELECT i, i % 7, 'r' || (i % 20) FROM generate_series(1, 2000) i; CREATE INDEX lcd_region ON lcd USING lion (region); VACUUM (FREEZE, ANALYZE) lcf; VACUUM (FREEZE, ANALYZE) lcd; /* * lcg_cost() is the estimated total cost of q's plan with setting `guc` at * `val` - its default when NULL - and the other settings in sw, name and * value in turn, all for the call alone; with no setting named, at whatever * the session has. */ CREATE FUNCTION lcg_cost(q text, guc text DEFAULT NULL, val text DEFAULT NULL, sw text[] DEFAULT '{}') RETURNS float8 LANGUAGE plpgsql AS $$ DECLARE p json; i int; BEGIN IF guc IS NOT NULL THEN PERFORM set_config(guc, coalesce(val, (SELECT boot_val FROM pg_settings WHERE name = guc)), true); END IF; FOR i IN 1 .. coalesce(array_length(sw, 1), 0) BY 2 LOOP PERFORM set_config(sw[i], sw[i + 1], true); END LOOP; EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO p; RETURN (p->0->'Plan'->>'Total Cost')::float8; END $$; /* The top two nodes of q's plan, with setting `guc` at `val` for the call. */ CREATE FUNCTION lcg_plan(q text, guc text, val text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE ln text; res text[] := '{}'; BEGIN PERFORM set_config(guc, coalesce(val, (SELECT boot_val FROM pg_settings WHERE name = guc)), true); FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP IF cardinality(res) < 2 AND (cardinality(res) = 0 OR ln ~ '^\s*->') THEN res := res || regexp_replace(btrim(regexp_replace(ln, '->', '')), ' (on|using) .*$', ''); END IF; END LOOP; RETURN array_to_string(res, ' / '); END $$; -- ---------- 2. each is read ---------- -- An index scan's (DESIGN.md §29.11): what a plain scan pays to fetch a row -- past the first on its heap page. 515 rows on some 400 pages: at 0 the -- plain scan costs what the bitmap heap scan does, and a tie goes to it; at -- 100 its fetches are the dearer. SET pg_lion.enable_count_pushdown = off; SELECT lcg_cost(q, s, '0', sw) < lcg_cost(q, s, NULL, sw) AS lowered_cheaper, lcg_cost(q, s, '100', sw) > lcg_cost(q, s, NULL, sw) AS raised_dearer FROM (VALUES ('SELECT sum(id) FROM lcg WHERE g = 5', 'pg_lion.plain_fetch_row_cost', '{enable_seqscan, off, enable_bitmapscan, off}'::text[])) v(q, s, sw); lowered_cheaper | raised_dearer -----------------+--------------- t | t (1 row) SELECT lcg_plan('SELECT sum(id) FROM lcg WHERE g = 5', 'pg_lion.plain_fetch_row_cost', '0'); lcg_plan ------------------------ Aggregate / Index Scan (1 row) SELECT lcg_plan('SELECT sum(id) FROM lcg WHERE g = 5', 'pg_lion.plain_fetch_row_cost', '100'); lcg_plan ------------------------------ Aggregate / Bitmap Heap Scan (1 row) RESET pg_lion.enable_count_pushdown; -- A count's (DESIGN.md §10, "The units"): each count a GROUP BY makes, one -- per entry of its column. At its default the count pushdown answers the -- hundred groups; at a hundred times that, the hash aggregate does. SELECT lcg_cost(q, s, '0', sw) < lcg_cost(q, s, NULL, sw) AS lowered_cheaper, lcg_cost(q, s, '100', sw) > lcg_cost(q, s, NULL, sw) AS raised_dearer FROM (VALUES ('SELECT g, count(*) FROM lcg GROUP BY g', 'pg_lion.entry_count_cost', '{enable_seqscan, off, enable_bitmapscan, off, pg_lion.enable_plain_scan, off}'::text[])) v(q, s, sw); lowered_cheaper | raised_dearer -----------------+--------------- t | t (1 row) SELECT lcg_plan('SELECT g, count(*) FROM lcg GROUP BY g', 'pg_lion.entry_count_cost', NULL); lcg_plan ------------------------- Custom Scan (LionCount) (1 row) SELECT lcg_plan('SELECT g, count(*) FROM lcg GROUP BY g', 'pg_lion.entry_count_cost', '5000'); lcg_plan -------------------------- HashAggregate / Seq Scan (1 row) -- The FK-side join's (DESIGN.md §27): each count, one per dimension row. At -- its default the node counts the region's hundred rows' keys; at a hundred -- times that, a hash join is the cheaper. SELECT lcg_cost(q, s, '0', sw) < lcg_cost(q, s, NULL, sw) AS lowered_cheaper, lcg_cost(q, s, '100', sw) > lcg_cost(q, s, NULL, sw) AS raised_dearer FROM (VALUES ('SELECT d.attr, count(*) FROM lcf f JOIN lcd d ON f.fk = d.pk WHERE d.region = ''r1'' GROUP BY d.attr', 'pg_lion.fkjoin_count_cost', '{enable_hashjoin, off, enable_mergejoin, off, enable_nestloop, off}'::text[])) v(q, s, sw); lowered_cheaper | raised_dearer -----------------+--------------- t | t (1 row) SELECT lcg_plan('SELECT d.attr, count(*) FROM lcf f JOIN lcd d ON f.fk = d.pk WHERE d.region = ''r1'' GROUP BY d.attr', 'pg_lion.fkjoin_count_cost', NULL); lcg_plan -------------------------------------------------- Finalize HashAggregate / Custom Scan (LionCount) (1 row) SELECT lcg_plan('SELECT d.attr, count(*) FROM lcf f JOIN lcd d ON f.fk = d.pk WHERE d.region = ''r1'' GROUP BY d.attr', 'pg_lion.fkjoin_count_cost', '2500'); lcg_plan --------------------------- HashAggregate / Hash Join (1 row) -- A range collected as a source's (DESIGN.md §32, "What collecting a range -- costs"): each fold of the union it is collected into. fk < 1000 is half -- of lcf's keys, scattered over its heap, and a source of the GROUP BY x. SELECT lcg_cost(q, s, '0', sw) < lcg_cost(q, s, NULL, sw) AS lowered_cheaper, lcg_cost(q, s, '100000', sw) > lcg_cost(q, s, NULL, sw) AS raised_dearer FROM (VALUES ('SELECT x, count(*) FROM lcf WHERE fk < 1000 GROUP BY x', 'pg_lion.range_fold_cost', '{enable_seqscan, off, enable_bitmapscan, off, pg_lion.enable_plain_scan, off}'::text[])) v(q, s, sw); lowered_cheaper | raised_dearer -----------------+--------------- t | t (1 row) -- ---------- 3. a SET LOCAL ends with its transaction ---------- SELECT lcg_cost('SELECT g, count(*) FROM lcg GROUP BY g') AS before \gset BEGIN; SET LOCAL pg_lion.entry_count_cost = 5000; SHOW pg_lion.entry_count_cost; pg_lion.entry_count_cost -------------------------- 5000 (1 row) EXPLAIN (COSTS OFF) SELECT g, count(*) FROM lcg GROUP BY g; QUERY PLAN ----------------------- HashAggregate Group Key: g -> Seq Scan on lcg (3 rows) SELECT lcg_cost('SELECT g, count(*) FROM lcg GROUP BY g') > :before AS dearer; dearer -------- t (1 row) ROLLBACK; SHOW pg_lion.entry_count_cost; pg_lion.entry_count_cost -------------------------- 50 (1 row) EXPLAIN (COSTS OFF) SELECT g, count(*) FROM lcg GROUP BY g; QUERY PLAN --------------------------- Custom Scan (LionCount) Lion Indexes: lcg_g (g) Group Key: g (3 rows) SELECT lcg_cost('SELECT g, count(*) FROM lcg GROUP BY g') = :before AS same_cost; same_cost ----------- t (1 row) -- a grouped count of a heap the visibility map vouches for half of: the node, -- at the grouped recheck's price, against a hash aggregate at the old one CREATE TABLE lcv WITH (autovacuum_enabled = off, fillfactor = 90) AS SELECT i AS id, i % 200 AS g, repeat('x', 64) AS pad FROM generate_series(1, 200000) i; CREATE INDEX lcv_g ON lcv USING lion (g); VACUUM (FREEZE, ANALYZE) lcv; UPDATE lcv SET pad = repeat('y', 64) WHERE id % 100 = 0; ANALYZE lcv; EXPLAIN (COSTS OFF) SELECT g, count(*) FROM lcv GROUP BY g; QUERY PLAN --------------------------- Custom Scan (LionCount) Lion Indexes: lcv_g (g) Group Key: g (3 rows) BEGIN; SET LOCAL pg_lion.recheck_group_tid_cost = 6; EXPLAIN (COSTS OFF) SELECT g, count(*) FROM lcv GROUP BY g; QUERY PLAN ----------------------- HashAggregate Group Key: g -> Seq Scan on lcv (3 rows) ROLLBACK; DROP TABLE lcv; DROP FUNCTION lcg_cost(text, text, text, text[]); DROP FUNCTION lcg_plan(text, text, text); DROP TABLE lcg, lcf, lcd;