-- SiftAgg over the wide leaves of a sift_col index as keys, aggregates and values: numeric of more than 18 digits -- or of any other shape, money and interval. The scan reads a wide leaf as a dictionary of the unit's distinct values and hands them to -- PostgreSQL's own functions, so a key, an aggregate, an OR, a FILTER, a CASE or an expression gives the rows of the normal plan, the -- spelling of equal values (1.0 and 1.00, 1 mon and 30 days) included: the one of the first row, as a hash aggregate shows it. CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_helpers; SET max_parallel_workers_per_gather = 0; SET seq_page_cost = 1000; SET random_page_cost = 1000; SET client_min_messages = warning; SET lc_monetary = 'C'; CREATE TABLE wa (id int, grp int, qty int, n38 numeric(38, 10), n19 numeric(19, 2), img numeric, neg numeric(5, -2), m money, iv interval) WITH (autovacuum_enabled = false); INSERT INTO wa SELECT i, i % 7, i % 100, CASE WHEN i % 13 = 0 THEN NULL WHEN i % 17 = 0 THEN 'NaN' WHEN i % 19 = 0 THEN 9999999999999999999999999999.9999999999 WHEN i % 29 = 0 THEN -9999999999999999999999999999.9999999999 WHEN i % 23 = 0 THEN 0 WHEN i % 2 = 0 THEN round(((i * 7919) % 2000001 - 1000000) / 100.0, 10) ELSE round(mod(i::numeric * 1234567890123456789012345.6789012345, 9999999999999999999999999999) * CASE WHEN i % 3 = 0 THEN -1 ELSE 1 END, 10) END, CASE WHEN i % 11 = 0 THEN NULL WHEN i % 17 = 0 THEN 'NaN' WHEN i % 31 = 0 THEN 99999999999999999.99 WHEN i % 37 = 0 THEN -99999999999999999.99 ELSE ((i * 104729) % 2000001 - 1000000) / 100.0 END, CASE WHEN i % 10 = 0 THEN NULL WHEN i % 41 = 0 THEN 'NaN' WHEN i % 43 = 0 THEN 'Infinity' WHEN i % 47 = 0 THEN '-Infinity' WHEN i % 53 = 0 THEN 10::numeric ^ 400 + i WHEN i % 59 = 0 THEN -(10::numeric ^ 400) WHEN i % 61 = 0 THEN 1 / 10::numeric ^ 300 WHEN i % 5 = 0 THEN (ARRAY[1.0, 1.00, 1, 1.000, 1.5, 1.50])[1 + i % 6] ELSE round(((i * 7919) % 200001 - 100000) / 7.0, 1 + i % 5) END, CASE WHEN i % 6 = 0 THEN NULL ELSE ((i % 300) - 150) * 100 END, CASE WHEN i % 9 = 0 THEN NULL WHEN i % 101 = 0 THEN '92233720368547758.07'::money WHEN i % 103 = 0 THEN '-92233720368547758.08'::money ELSE (((i * 7919) % 200001 - 100000)::numeric / 100)::money END, CASE WHEN i % 12 = 0 THEN NULL WHEN i % 71 = 0 THEN 'infinity' WHEN i % 73 = 0 THEN '-infinity' WHEN i % 79 = 0 THEN '2147483647 months 2147483647 days 9223372036854775807 us'::interval WHEN i % 83 = 0 THEN '-2147483648 months -2147483648 days -9223372036854775808 us'::interval ELSE (ARRAY['1 month', '30 days', '720 hours', '1 mon 1 day', '31 days', '1 day 1 hour', '25 hours', '-1 month', '-30 days', '0', '00:00:00.000001', '-00:00:00.000001', '1 year', '12 mons', '365 days', '2 mons', '59 days', '60 days'])[1 + i % 18]::interval END FROM generate_series(1, 20000) i; CREATE INDEX wa_col ON wa USING sift_col (id, grp, qty, n38, n19, img, neg, m, iv); VACUUM (FREEZE, ANALYZE) wa; -- the rows of both plans as the text PostgreSQL prints them (the columns of a query are named), or their error; the off plan hashes (a sort keeps an arbitrary one of equal keys) CREATE FUNCTION chk(q text) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.agg_check_outcome(q) $$; -- the numeric a decimal of two lanes becomes, for every scale and size: the digits of a group of 10000, the weight and the display scale CREATE TABLE ws (id int, n0 numeric(38, 0), n1 numeric(38, 1), n3 numeric(38, 3), n4 numeric(38, 4), n5 numeric(38, 5), n19 numeric(38, 19), n38 numeric(38, 38), n20 numeric(20, 20), n30 numeric(30, 25), nz numeric(19, 0)) WITH (autovacuum_enabled = false); INSERT INTO ws SELECT row_number() OVER (), mod(x, 10::numeric ^ 38) * sgn, mod(x, 10::numeric ^ 38) * sgn * 1e-1, mod(x, 10::numeric ^ 38) * sgn * 1e-3, mod(x, 10::numeric ^ 38) * sgn * 1e-4, mod(x, 10::numeric ^ 38) * sgn * 1e-5, mod(x, 10::numeric ^ 38) * sgn * 1e-19, mod(x, 10::numeric ^ 38) * sgn * 1e-38, mod(x, 10::numeric ^ 20) * sgn * 1e-20, mod(x, 10::numeric ^ 30) * sgn * 1e-25, mod(x, 10::numeric ^ 19) * sgn FROM (SELECT (10::numeric ^ i * c + d) AS x, s AS sgn FROM generate_series(0, 38) i, unnest(ARRAY[1, 7, 9999, 12345]) c, unnest(ARRAY[-1, 0, 1]) d, unnest(ARRAY[1, -1]) s) g; INSERT INTO ws SELECT 10000 + i, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM generate_series(1, 5) i; CREATE INDEX ws_col ON ws USING sift_col (id, n0, n1, n3, n4, n5, n19, n38, n20, n30, nz); VACUUM (FREEZE, ANALYZE) ws; SELECT chk($$SELECT n0 AS k, count(*) AS c FROM ws GROUP BY n0$$) AS s0, chk($$SELECT n1 AS k, count(*) AS c FROM ws GROUP BY n1$$) AS s1, chk($$SELECT n3 AS k, count(*) AS c FROM ws GROUP BY n3$$) AS s3, chk($$SELECT n4 AS k, count(*) AS c FROM ws GROUP BY n4$$) AS s4, chk($$SELECT n5 AS k, count(*) AS c FROM ws GROUP BY n5$$) AS s5, chk($$SELECT n19 AS k, count(*) AS c FROM ws GROUP BY n19$$) AS s19, chk($$SELECT n38 AS k, count(*) AS c FROM ws GROUP BY n38$$) AS s38, chk($$SELECT n20 AS k, count(*) AS c FROM ws GROUP BY n20$$) AS p20, chk($$SELECT n30 AS k, count(*) AS c FROM ws GROUP BY n30$$) AS p30, chk($$SELECT nz AS k, count(*) AS c FROM ws GROUP BY nz$$) AS p19; SELECT chk($$SELECT sum(n1) AS a, min(n3) AS b, max(n19) AS c, avg(n38) AS d, sum(n20) AS e, min(n30) AS f, max(nz) AS g FROM ws$$) AS aggs, chk($$SELECT n0 > 0 AS k, sum(n5) AS a, count(DISTINCT n4) AS b FROM ws GROUP BY 1$$) AS grouped; DROP TABLE ws; -- keys SELECT chk($$SELECT n38 AS k, count(*) AS c FROM wa GROUP BY n38$$) AS n38_key, chk($$SELECT n19 AS k, count(*) AS c, sum(qty) AS s FROM wa GROUP BY n19$$) AS n19_key, chk($$SELECT img AS k, count(*) AS c FROM wa GROUP BY img$$) AS image_key, chk($$SELECT neg AS k, count(*) AS c FROM wa GROUP BY neg$$) AS negative_scale_key, chk($$SELECT iv AS k, count(*) AS c FROM wa GROUP BY iv$$) AS interval_key, chk($$SELECT m AS k, count(*) AS c, min(m) AS a, max(m) AS b FROM wa GROUP BY m$$) AS money_key, chk($$SELECT grp AS g, n19 AS k, count(*) AS c FROM wa GROUP BY grp, n19$$) AS two_keys, chk($$SELECT n38 + 1 AS k, count(*) AS c FROM wa GROUP BY n38 + 1$$) AS expression_key, chk($$SELECT n38 > 0 AS k, count(*) AS c FROM wa GROUP BY 1$$) AS predicate_key, chk($$SELECT iv AS k, sum(n38) AS s, avg(iv) AS a FROM wa WHERE iv < '1000 years' AND iv > '-1000 years' GROUP BY iv$$) AS key_and_sums; -- aggregates SELECT chk($$SELECT grp AS g, count(*) AS c, count(n38) AS cn, min(n38) AS mn, max(n38) AS mx, sum(n38) AS s, avg(n38) AS a FROM wa GROUP BY grp$$) AS n38_aggs, chk($$SELECT grp AS g, sum(n19) AS s, avg(n19) AS a, min(n19) AS mn, max(n19) AS mx FROM wa GROUP BY grp$$) AS n19_aggs, chk($$SELECT grp AS g, sum(img) AS s, avg(img) AS a, min(img) AS mn, max(img) AS mx FROM wa GROUP BY grp$$) AS image_aggs, chk($$SELECT grp AS g, min(iv) AS mn, max(iv) AS mx, count(DISTINCT iv) AS d FROM wa GROUP BY grp$$) AS interval_minmax, chk($$SELECT grp AS g, sum(iv) AS s FROM wa WHERE iv < 'infinity' AND iv > '-infinity' AND iv < '1000 years' GROUP BY grp$$) AS interval_sum, chk($$SELECT avg(iv) AS a, sum(iv) AS s FROM wa WHERE iv < 'infinity' AND iv > '-infinity'$$) AS interval_avg, chk($$SELECT sum(iv) AS s FROM wa$$) AS interval_sum_overflow, chk($$SELECT count(DISTINCT n38) AS a, count(DISTINCT img) AS b, count(DISTINCT n19) AS c FROM wa$$) AS count_distinct, chk($$SELECT sum(n38) AS s FROM wa$$) AS sum_all, chk($$SELECT min(img) AS a, max(img) AS b, min(iv) AS c, max(iv) AS d, min(n38) AS e, max(n38) AS f FROM wa WHERE grp = 3$$) AS ties_minmax, chk($$SELECT grp AS g, string_agg(DISTINCT iv::text, ',' ORDER BY iv::text) AS s FROM wa GROUP BY grp$$) AS ordered_distinct, chk($$SELECT grp AS g, count(DISTINCT n38::text) AS c FROM wa GROUP BY grp$$) AS text_cast; -- money SELECT chk($$SELECT grp AS g, sum(m) AS s FROM wa WHERE m < '1000'::money AND m > '-1000'::money GROUP BY grp$$) AS money_sum, chk($$SELECT grp AS g, avg(m::numeric) AS a FROM wa GROUP BY grp$$) AS money_avg, chk($$SELECT sum(m) AS s FROM wa$$) AS money_overflow, chk($$SELECT count(DISTINCT m) AS c, min(m) AS a, max(m) AS b FROM wa$$) AS money_distinct; -- under OR, NOT, FILTER, CASE and expressions SELECT chk($$SELECT count(*) AS c FROM wa WHERE n38 > 100 OR iv > '1 mon'$$) AS or_, chk($$SELECT grp AS g, count(*) AS c FROM wa WHERE NOT (n38 > 100 AND iv > '1 mon') GROUP BY grp$$) AS not_and, chk($$SELECT grp AS g, count(*) AS c FROM wa WHERE n38 > 100 OR (m < '0' AND img IS NULL) GROUP BY grp$$) AS or_and, chk($$SELECT grp AS g, count(*) FILTER (WHERE n38 > 100) AS c1, sum(qty) FILTER (WHERE img > 1.0 OR iv < '2 days') AS c2, max(id) FILTER (WHERE NOT n19 > 0) AS c3 FROM wa GROUP BY grp$$) AS filter_, chk($$SELECT CASE WHEN n38 > 100 THEN 'a' WHEN iv > '1 mon' THEN 'b' ELSE 'c' END AS k, count(*) AS c FROM wa GROUP BY 1$$) AS case_, chk($$SELECT count(*) AS c FROM wa WHERE n38 IS DISTINCT FROM 0 AND img IS NOT DISTINCT FROM 1.00$$) AS distinct_from, chk($$SELECT count(*) AS c FROM wa WHERE n38 = ANY (ARRAY[0, 1.5, 3])$$) AS any_array, chk($$SELECT count(*) AS c FROM wa WHERE n38 + 1 > 5$$) AS expression_, chk($$SELECT count(*) AS c FROM wa WHERE img::text = '1'$$) AS cast_, chk($$SELECT count(*) AS c FROM wa WHERE n38 > 1e60 OR iv > interval '1 month'$$) AS unreachable_or; -- the empty groups, a window of the table, the other keys SELECT chk($$SELECT n38 AS k, count(*) AS c FROM wa WHERE id < 0 GROUP BY n38$$) AS empty, chk($$SELECT sum(n38) AS s, min(iv) AS a, count(DISTINCT img) AS c FROM wa WHERE id < 0$$) AS empty_aggs, chk($$SELECT iv AS k, count(*) AS c FROM wa WHERE id BETWEEN 5000 AND 7000 GROUP BY iv$$) AS window_, chk($$SELECT grp AS g, count(*) AS c, sum(n38) AS s FROM wa WHERE iv > interval '1 day' AND n38 IS NOT NULL GROUP BY grp$$) AS conjunct_and_agg; -- a few rows left of the unit (a conjunct of the source keeps 1%, or none): the dictionary of a wide leaf is made for those rows SELECT chk($$SELECT n38 AS k, count(*) AS c, sum(n19) AS s FROM wa WHERE qty = 14 GROUP BY n38$$) AS few_n38_key, chk($$SELECT img AS k, count(*) AS c, min(img) AS a FROM wa WHERE qty = 41 GROUP BY img$$) AS few_image_key, chk($$SELECT iv AS k, count(*) AS c, max(iv) AS a FROM wa WHERE qty = 7 GROUP BY iv$$) AS few_interval_key, chk($$SELECT m AS k, count(*) AS c FROM wa WHERE qty = 53 GROUP BY m$$) AS few_money_key, chk($$SELECT grp AS g, count(*) AS c, sum(n38) AS s, avg(n19) AS a, min(img) AS b, max(iv) AS d FROM wa WHERE qty = 17 GROUP BY grp$$) AS few_aggs, chk($$SELECT count(DISTINCT n38) AS a, count(DISTINCT iv) AS b, sum(img) AS c FROM wa WHERE qty IN (3, 90)$$) AS few_distinct, chk($$SELECT n38 AS k, count(*) AS c FROM wa WHERE qty = 5 AND n38 > 1 GROUP BY n38$$) AS few_wide_conjunct, chk($$SELECT n38 AS k, sum(n19) AS s, min(iv) AS a FROM wa WHERE qty = -1 GROUP BY n38$$) AS few_none; -- parameters: a generic plan PREPARE p_k(numeric) AS SELECT iv AS k, count(*) AS c, sum(n38) AS s FROM wa WHERE n38 > $1 OR img = $1 GROUP BY iv; SET plan_cache_mode = force_generic_plan; SELECT chk($$EXECUTE p_k(1234.5)$$) AS generic, chk($$EXECUTE p_k('NaN')$$) AS generic_nan, chk($$EXECUTE p_k(NULL)$$) AS generic_null; RESET plan_cache_mode; DEALLOCATE ALL; -- the write buffer, a deleted row, an updated one and a merged index INSERT INTO wa SELECT 30000 + i, i % 7, i % 100, CASE WHEN i % 7 = 0 THEN 'NaN' ELSE i * 1234567.1234567891 END, i / 3.0, CASE WHEN i % 5 = 0 THEN 'NaN' ELSE round(i / 7.0, i % 9) END, 100, (i * 17 || '.' || i % 100)::money, make_interval(months => i % 14, days => i % 40) FROM generate_series(1, 3000) i; CREATE FUNCTION shapes(tag text) RETURNS text LANGUAGE sql AS $$ SELECT concat_ws(' ', tag, chk('SELECT n38 AS k, count(*) AS c FROM wa GROUP BY n38'), chk('SELECT img AS k, count(*) AS c, min(img) AS a, max(img) AS b FROM wa GROUP BY img'), chk('SELECT iv AS k, count(*) AS c, min(iv) AS a, max(iv) AS b FROM wa GROUP BY iv'), chk('SELECT grp AS g, sum(n19) AS s, avg(n19) AS a, count(DISTINCT n38) AS d FROM wa GROUP BY grp'), chk('SELECT grp AS g, count(*) FILTER (WHERE n38 > 100 OR iv > ''1 mon'') AS c FROM wa GROUP BY grp')) $$; SELECT shapes('buffered'); DELETE FROM wa WHERE id % 5 = 1; UPDATE wa SET n38 = round(n38 / 2, 10), m = m / 2, iv = iv + interval '1 day' WHERE id % 11 = 2 AND id % 71 <> 0 AND id % 73 <> 0 AND id % 79 <> 0 AND id % 83 <> 0 AND id % 101 <> 0 AND id % 103 <> 0; SELECT shapes('updated'); SELECT sift.col_promote('wa_col'); VACUUM wa; SELECT sift.col_merge('wa_col', true); SELECT shapes('merged'); -- parallel workers: the aggregates run in them, a key of equal spellings does not RESET seq_page_cost; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_table_scan_size = 0; SET max_parallel_workers_per_gather = 2; SET seq_page_cost = 1000; SELECT chk($$SELECT grp AS g, count(*) AS c, sum(n38) AS s, min(iv) AS a FROM wa GROUP BY grp$$) AS parallel_aggs, chk($$SELECT iv AS k, count(*) AS c FROM wa GROUP BY iv$$) AS parallel_key; DROP TABLE wa; DROP FUNCTION chk(text), shapes(text); DROP EXTENSION pg_sift_helpers; DROP EXTENSION pg_sift;