Postgres 13

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

CREATE TABLE tbl (
  asdf NUMERIC,
  poiu VARCHAR(3),
  oiuy DATE,
  sdfg NUMERIC,
  zxcv NUMERIC,
  mnbv NUMERIC,
  qwer NUMERIC,
  wert NUMERIC
);

INSERT INTO tbl
SELECT 1
     , LPAD(g::text, 3, '0' )
     , now()::date - (random()*100)::int
     , 10 - g
     , g
     , ABS(5-g)
     , random()::numeric
FROM   generate_series(1,10) g;

CREATE TABLE



INSERT 0 10


-- dynamic function
CREATE OR REPLACE FUNCTION f_min_of(_tbl anyelement)
  RETURNS SETOF anyelement 
  LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE (
   SELECT format('SELECT (t::%2$s).* FROM (SELECT min(%1$s) FROM %2$s) t'
                , string_agg(quote_ident(attname), '), min(' ORDER BY attnum)
                , pg_typeof(_tbl)::text)
   FROM   pg_attribute
   WHERE  attrelid = pg_typeof(_tbl)::text::regclass
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   );
END
$func$;

CREATE FUNCTION


-- original values
TABLE tbl;


asdf
poiu
oiuy
sdfg
zxcv
mnbv
qwer
wert


1
001
2023-11-06
9
1
4
0.99838001131069
null

1
002
2023-11-08
8
2
3
0.0310758431521627
null

1
003
2024-01-10
7
3
2
0.661026960925543
null

1
004
2024-01-04
6
4
1
0.427434390937105
null

1
005
2024-01-02
5
5
0
0.33285830501713
null

1
006
2023-10-26
4
6
1
0.0139564889613233
null

1
007
2024-01-14
3
7
2
0.940349906867116
null

1
008
2024-01-31
2
8
3
0.3490542940966
null

1
009
2023-12-17
1
9
4
0.78220225897353
null

1
010
2023-11-25
0
10
5
0.72551412950639
null



SELECT 10


SELECT * FROM f_min_of(NULL::tbl);


asdf
poiu
oiuy
sdfg
zxcv
mnbv
qwer
wert


1
001
2023-10-26
0
1
0
0.0139564889613233
null



SELECT 1


-- This generates the query
SELECT format('SELECT (t::tbl).* FROM (SELECT min(%s) FROM %s) t'
             , string_agg(quote_ident(attname), '), min(' ORDER BY attnum)
             , 'tbl'::regclass
             )
FROM   pg_attribute 
WHERE  attrelid = 'tbl'::regclass
AND    NOT attisdropped
AND    attnum > 0;


format


SELECT (t::tbl).* FROM (SELECT min(asdf), min(poiu), min(oiuy), min(sdfg), min(zxcv), min(mnbv), min(qwer), min(wert) FROM tbl) t



SELECT 1


-- the generated query
SELECT (t::tbl).*
FROM  (
   SELECT min(asdf), min(poiu), min(oiuy), min(sdfg)
        , min(zxcv), min(mnbv), min(qwer), min(wert)
   FROM   tbl
   ) t;


asdf
poiu
oiuy
sdfg
zxcv
mnbv
qwer
wert


1
001
2023-10-26
0
1
0
0.0139564889613233
null



SELECT 1


ANALYZE tbl;

SELECT * FROM pg_stats
WHERE tablename = 'tbl'
AND   schemaname = 'public';

ANALYZE




schemaname
tablename
attname
inherited
null_frac
avg_width
n_distinct
most_common_vals
most_common_freqs
histogram_bounds
correlation
most_common_elems
most_common_elem_freqs
elem_count_histogram


public
tbl
asdf
f
0
5
1
{1}
{1}
null
1
null
null
null

public
tbl
poiu
f
0
4
-1
null
null
{001,002,003,004,005,006,007,008,009,010}
1
null
null
null

public
tbl
oiuy
f
0
4
-1
null
null
{2023-10-26,2023-11-06,2023-11-08,2023-11-25,2023-12-17,2024-01-02,2024-01-04,2024-01-10,2024-01-14,2024-01-31}
0.26060605
null
null
null

public
tbl
sdfg
f
0
4
-1
null
null
{0,1,2,3,4,5,6,7,8,9}
-1
null
null
null

public
tbl
zxcv
f
0
5
-1
null
null
{1,2,3,4,5,6,7,8,9,10}
1
null
null
null

public
tbl
mnbv
f
0
4
-0.6
{1,2,3,4}
{0.2,0.2,0.2,0.2}
{0,5}
0.3939394
null
null
null

public
tbl
qwer
f
0
11
-1
null
null
{0.0139564889613233,0.0310758431521627,0.33285830501713,0.3490542940966,0.427434390937105,0.661026960925543,0.72551412950639,0.78220225897353,0.940349906867116,0.99838001131069}
0.09090909
null
null
null

public
tbl
wert
f
1
0
0
null
null
null
null
null
null
null



SELECT 8

asdf	poiu	oiuy	sdfg	zxcv	mnbv	qwer	wert
1	001	2023-11-06	9	1	4	0.99838001131069	null
1	002	2023-11-08	8	2	3	0.0310758431521627	null
1	003	2024-01-10	7	3	2	0.661026960925543	null
1	004	2024-01-04	6	4	1	0.427434390937105	null
1	005	2024-01-02	5	5	0	0.33285830501713	null
1	006	2023-10-26	4	6	1	0.0139564889613233	null
1	007	2024-01-14	3	7	2	0.940349906867116	null
1	008	2024-01-31	2	8	3	0.3490542940966	null
1	009	2023-12-17	1	9	4	0.78220225897353	null
1	010	2023-11-25	0	10	5	0.72551412950639	null

schemaname	tablename	attname	inherited	null_frac	avg_width	n_distinct	most_common_vals	most_common_freqs	histogram_bounds	correlation	most_common_elems	most_common_elem_freqs	elem_count_histogram
public	tbl	asdf	f	0	5	1	{1}	{1}	null	1	null	null	null
public	tbl	poiu	f	0	4	-1	null	null	{001,002,003,004,005,006,007,008,009,010}	1	null	null	null
public	tbl	oiuy	f	0	4	-1	null	null	{2023-10-26,2023-11-06,2023-11-08,2023-11-25,2023-12-17,2024-01-02,2024-01-04,2024-01-10,2024-01-14,2024-01-31}	0.26060605	null	null	null
public	tbl	sdfg	f	0	4	-1	null	null	{0,1,2,3,4,5,6,7,8,9}	-1	null	null	null
public	tbl	zxcv	f	0	5	-1	null	null	{1,2,3,4,5,6,7,8,9,10}	1	null	null	null
public	tbl	mnbv	f	0	4	-0.6	{1,2,3,4}	{0.2,0.2,0.2,0.2}	{0,5}	0.3939394	null	null	null
public	tbl	qwer	f	0	11	-1	null	null	{0.0139564889613233,0.0310758431521627,0.33285830501713,0.3490542940966,0.427434390937105,0.661026960925543,0.72551412950639,0.78220225897353,0.940349906867116,0.99838001131069}	0.09090909	null	null	null
public	tbl	wert	f	1	0	0	null	null	null	null	null	null	null