Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- different hashes for Postgres 11-14 vs 15 vs 16
select version();

-- md5 aggregate sfunc
CREATE FUNCTION md5_agg_sfunc(text, anyelement)
       RETURNS text
       LANGUAGE sql
AS
$$
  SELECT md5($1 || $2::text)
$$;

-- md5 aggregate function
CREATE AGGREGATE md5_agg (ORDER BY anyelement)
(
  STYPE = text,
  SFUNC = md5_agg_sfunc,
  INITCOND = ''
);

-- test data. hashes change for a row number >= 79022 
CREATE TABLE t_test (a int, b int);
INSERT INTO t_test
       SELECT   x, x + 10
       FROM     generate_series(1, 79022) AS x;

-- whole table hash H1
SELECT md5_agg() WITHIN GROUP (ORDER BY t_test ASC)
FROM t_test;

-- ordered set grouped hashes differ across postgres versions
-- grouped hash H2, all in one group: H2 identical with H1 for PG version >= 15
SELECT
  a % 1 AS grp,
  md5_agg() WITHIN GROUP (ORDER BY t_test)
FROM t_test
GROUP BY a % 1;

-- hashes change for different postgres versions
SELECT
  a % 3 AS grp,
  md5_agg() WITHIN GROUP (ORDER BY t_test)
FROM t_test
GROUP BY a % 3;

version
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE FUNCTION

CREATE AGGREGATE

CREATE TABLE

INSERT 0 79022

md5_agg
f57bddccbd1e14cbe7dc3020e879d778

SELECT 1

grp	md5_agg
0	f57bddccbd1e14cbe7dc3020e879d778

SELECT 1

grp	md5_agg
0	67ee866881833cb60a64e1a8662255b3
1	268fde93243588d529ba0250ef8f7426
2	5913a5b20c5139777301d7aaac7c424d

SELECT 3