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

SELECT

(SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),

(SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random() LIMIT 1)

FROM generate_series(1, 6) as b(n);

explain analyze verbose

SELECT

(SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),

(SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random() LIMIT 1)

FROM generate_series(1, 6) as b(n);

val	val	val
60	50	2.1
60	50	2.1
60	50	2.1
60	50	2.1
60	50	2.1
60	50	2.1

SELECT 6

QUERY PLAN
Function Scan on pg_catalog.generate_series b (cost=0.26..0.32 rows=6 width=40) (actual time=0.071..0.073 rows=6 loops=1)
Output: $0, $1, $2
Function Call: generate_series(1, 6)
InitPlan 1 (returns $0)
-> Limit (cost=0.09..0.09 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=1)
Output: val.val, (random())
-> Sort (cost=0.09..0.10 rows=5 width=12) (actual time=0.008..0.008 rows=1 loops=1)
Output: val.val, (random())
Sort Key: (random())
Sort Method: top-N heapsort Memory: 25kB
-> Function Scan on pg_catalog.unnest val (cost=0.00..0.07 rows=5 width=12) (actual time=0.003..0.004 rows=5 loops=1)
Output: val.val, random()
Function Call: unnest('{50,60,80,100,200}'::integer[])
InitPlan 2 (returns $1)
-> Limit (cost=0.09..0.09 rows=1 width=12) (actual time=0.032..0.032 rows=1 loops=1)
Output: val_1.val, (random())
-> Sort (cost=0.09..0.10 rows=5 width=12) (actual time=0.032..0.032 rows=1 loops=1)
Output: val_1.val, (random())
Sort Key: (random())
Sort Method: top-N heapsort Memory: 25kB
-> Function Scan on pg_catalog.unnest val_1 (cost=0.00..0.07 rows=5 width=12) (actual time=0.018..0.019 rows=5 loops=1)
Output: val_1.val, random()
Function Call: unnest('{50,60,80,100,200}'::integer[])
InitPlan 3 (returns $2)
-> Limit (cost=0.07..0.08 rows=1 width=40) (actual time=0.026..0.026 rows=1 loops=1)
Output: val_2.val, (random())
-> Sort (cost=0.07..0.08 rows=4 width=40) (actual time=0.025..0.025 rows=1 loops=1)
Output: val_2.val, (random())
Sort Key: (random())
Sort Method: top-N heapsort Memory: 25kB
-> Function Scan on pg_catalog.unnest val_2 (cost=0.00..0.05 rows=4 width=40) (actual time=0.015..0.016 rows=4 loops=1)
Output: val_2.val, random()
Function Call: unnest('{1.5,1.8,2.1,2.5}'::numeric[])
Planning Time: 0.129 ms
Execution Time: 0.137 ms

EXPLAIN

SELECT

(SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random()+n-n LIMIT 1),

(SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random()+n-n LIMIT 1)

FROM generate_series(1, 6) as b(n);

explain analyze verbose

SELECT

(SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random()+n-n LIMIT 1),

(SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random()+n-n LIMIT 1)

FROM generate_series(1, 6) as b(n);

val	val	val
100	80	1.8
200	50	1.5
80	200	1.8
50	200	2.5
60	60	1.8
100	80	1.5

SELECT 6

QUERY PLAN
Function Scan on pg_catalog.generate_series b (cost=0.00..2.46 rows=6 width=40) (actual time=0.084..0.140 rows=6 loops=1)
Output: (SubPlan 1), (SubPlan 2), (SubPlan 3)
Function Call: generate_series(1, 6)
SubPlan 1
-> Limit (cost=0.14..0.14 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=6)
Output: val.val, (((random() + (b.n)::double precision) - (b.n)::double precision))
-> Sort (cost=0.14..0.15 rows=5 width=12) (actual time=0.006..0.006 rows=1 loops=6)
Output: val.val, (((random() + (b.n)::double precision) - (b.n)::double precision))
Sort Key: (((random() + (b.n)::double precision) - (b.n)::double precision))
Sort Method: top-N heapsort Memory: 25kB
-> Function Scan on pg_catalog.unnest val (cost=0.00..0.12 rows=5 width=12) (actual time=0.002..0.003 rows=5 loops=6)
Output: val.val, ((random() + (b.n)::double precision) - (b.n)::double precision)
Function Call: unnest('{50,60,80,100,200}'::integer[])
SubPlan 2
-> Limit (cost=0.14..0.14 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=6)
Output: val_1.val, (((random() + (b.n)::double precision) - (b.n)::double precision))
-> Sort (cost=0.14..0.15 rows=5 width=12) (actual time=0.006..0.006 rows=1 loops=6)
Output: val_1.val, (((random() + (b.n)::double precision) - (b.n)::double precision))
Sort Key: (((random() + (b.n)::double precision) - (b.n)::double precision))
Sort Method: top-N heapsort Memory: 25kB
-> Function Scan on pg_catalog.unnest val_1 (cost=0.00..0.12 rows=5 width=12) (actual time=0.002..0.003 rows=5 loops=6)
Output: val_1.val, ((random() + (b.n)::double precision) - (b.n)::double precision)
Function Call: unnest('{50,60,80,100,200}'::integer[])
SubPlan 3
-> Limit (cost=0.11..0.12 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=6)
Output: val_2.val, (((random() + (b.n)::double precision) - (b.n)::double precision))
-> Sort (cost=0.11..0.12 rows=4 width=40) (actual time=0.006..0.006 rows=1 loops=6)
Output: val_2.val, (((random() + (b.n)::double precision) - (b.n)::double precision))
Sort Key: (((random() + (b.n)::double precision) - (b.n)::double precision))
Sort Method: top-N heapsort Memory: 25kB
-> Function Scan on pg_catalog.unnest val_2 (cost=0.00..0.09 rows=4 width=40) (actual time=0.003..0.004 rows=4 loops=6)
Output: val_2.val, ((random() + (b.n)::double precision) - (b.n)::double precision)
Function Call: unnest('{1.5,1.8,2.1,2.5}'::numeric[])
Planning Time: 0.106 ms
Execution Time: 0.212 ms

EXPLAIN