Postgres 16

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?.

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

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

-- test data, two correlated columns
CREATE TABLE t_test (a int, b int);
INSERT INTO t_test
       SELECT   x, x + 10
       FROM     generate_series(1, 100000) AS x;

-- Result for ASC sort order is the same for all three criteria!
SELECT * FROM t_test ORDER BY t_test ASC LIMIT 3;
SELECT * FROM t_test ORDER BY t_test.a ASC LIMIT 3;
SELECT * FROM t_test ORDER BY t_test.b ASC LIMIT 3;

-- Result for DESC sort order is the same for all three criteria!
SELECT * FROM t_test ORDER BY t_test DESC LIMIT 3;
SELECT * FROM t_test ORDER BY t_test.a DESC LIMIT 3;
SELECT * FROM t_test ORDER BY t_test.b DESC LIMIT 3;

-- expected 2 hashes, hash H1 for columns 1-3 for ASC order, 
--                    hash H2 for columns 4-6 for DESC order
SELECT md5_agg() WITHIN GROUP (ORDER BY t_test ASC),
       md5_agg() WITHIN GROUP (ORDER BY t_test.a ASC),
       md5_agg() WITHIN GROUP (ORDER BY t_test.b ASC),
       md5_agg() WITHIN GROUP (ORDER BY t_test DESC),
       md5_agg() WITHIN GROUP (ORDER BY t_test.a DESC),
       md5_agg() WITHIN GROUP (ORDER BY t_test.b DESC)
FROM t_test;
-- received 3 hashes:
--               H3 for col. 1, 4
--               H4 for col. 2, 5
--               H5 for col. 3, 6
-- Sort order has no influence. 
-- Specification of order criterion (whole record or different columns) determines the result.

CREATE FUNCTION

CREATE AGGREGATE

CREATE TABLE

INSERT 0 100000

a	b
1	11
2	12
3	13

SELECT 3

a	b
1	11
2	12
3	13

SELECT 3

a	b
1	11
2	12
3	13

SELECT 3

a	b
100000	100010
99999	100009
99998	100008

SELECT 3

a	b
100000	100010
99999	100009
99998	100008

SELECT 3

a	b
100000	100010
99999	100009
99998	100008

SELECT 3

md5_agg	md5_agg	md5_agg	md5_agg	md5_agg	md5_agg
f324253132ef20f0fd7b233b33c77a6f	431d162cc455e4b3ef490915a0ca261c	0d0d50fec0a4c5636e14ad17286a56ce	f324253132ef20f0fd7b233b33c77a6f	431d162cc455e4b3ef490915a0ca261c	0d0d50fec0a4c5636e14ad17286a56ce

SELECT 1