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?.
-- Function without STRICT modifier (otherwise identical):
CREATE OR REPLACE FUNCTION f_sort_array(int[])
  RETURNS int[]
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$func$;

-- Function with STRICT modifier:
CREATE OR REPLACE FUNCTION f_sort_array_strict(int[])
  RETURNS int[]
  LANGUAGE sql IMMUTABLE STRICT AS
$func$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$func$;

CREATE FUNCTION

CREATE FUNCTION

CREATE TABLE tbl_nonull AS
SELECT ARRAY[(random() * 1000)::int, (random() * 1000)::int] AS arr
FROM   generate_series(1,10000);

SELECT 10000

CREATE TABLE tbl_null AS
SELECT NULL::int[] AS arr
FROM   generate_series(1,10000);

SELECT 10000

CREATE TABLE tbl_mix AS
(TABLE tbl_nonull LIMIT 5000)
UNION ALL 
(TABLE tbl_null LIMIT 5000);

SELECT 10000

VACUUM FULL ANALYZE tbl_nonull;

VACUUM

VACUUM FULL ANALYZE tbl_null;

VACUUM

VACUUM FULL ANALYZE tbl_mix;

VACUUM

---------- no NULL ----------
-- plain 1
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_nonull;

QUERY PLAN

Seq Scan on tbl_nonull (actual rows=10000 loops=1)

Planning Time: 0.170 ms

Execution Time: 4.354 ms

EXPLAIN

-- strict 1
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_nonull;

QUERY PLAN

Seq Scan on tbl_nonull (actual rows=10000 loops=1)

Planning Time: 0.052 ms

Execution Time: 39.271 ms

EXPLAIN

-- plain 2
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_nonull;

QUERY PLAN

Seq Scan on tbl_nonull (actual rows=10000 loops=1)

Planning Time: 0.118 ms

Execution Time: 3.314 ms

EXPLAIN

-- strict 2
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_nonull;

QUERY PLAN

Seq Scan on tbl_nonull (actual rows=10000 loops=1)

Planning Time: 0.079 ms

Execution Time: 37.244 ms

EXPLAIN

-- plain 3
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_nonull;

QUERY PLAN

Seq Scan on tbl_nonull (actual rows=10000 loops=1)

Planning Time: 0.107 ms

Execution Time: 3.315 ms

EXPLAIN

-- strict 3
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_nonull;

QUERY PLAN

Seq Scan on tbl_nonull (actual rows=10000 loops=1)

Planning Time: 0.081 ms

Execution Time: 38.132 ms

EXPLAIN

---------- all NULL ----------
-- plain 1
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_null;

QUERY PLAN

Seq Scan on tbl_null (actual rows=10000 loops=1)

Planning Time: 0.112 ms

Execution Time: 5.773 ms

EXPLAIN

-- strict 1
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_null;

QUERY PLAN

Seq Scan on tbl_null (actual rows=10000 loops=1)

Planning Time: 0.210 ms

Execution Time: 1.271 ms

EXPLAIN

-- plain 2
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_null;

QUERY PLAN

Seq Scan on tbl_null (actual rows=10000 loops=1)

Planning Time: 0.059 ms

Execution Time: 1.941 ms

EXPLAIN

-- strict 2
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_null;

QUERY PLAN

Seq Scan on tbl_null (actual rows=10000 loops=1)

Planning Time: 0.101 ms

Execution Time: 1.360 ms

EXPLAIN

-- plain 3
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_null;

QUERY PLAN

Seq Scan on tbl_null (actual rows=10000 loops=1)

Planning Time: 0.055 ms

Execution Time: 2.329 ms

EXPLAIN

-- strict 3
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_null;

QUERY PLAN

Seq Scan on tbl_null (actual rows=10000 loops=1)

Planning Time: 0.125 ms

Execution Time: 1.260 ms

EXPLAIN

---------- half NULL / half values ----------
-- plain 1
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_mix;

QUERY PLAN

Seq Scan on tbl_mix (actual rows=10000 loops=1)

Planning Time: 0.383 ms

Execution Time: 11.064 ms

EXPLAIN

-- strict 1
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_mix;

QUERY PLAN

Seq Scan on tbl_mix (actual rows=10000 loops=1)

Planning Time: 0.366 ms

Execution Time: 46.420 ms

EXPLAIN

-- plain 2
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_mix;

QUERY PLAN

Seq Scan on tbl_mix (actual rows=10000 loops=1)

Planning Time: 0.269 ms

Execution Time: 4.342 ms

EXPLAIN

-- strict 2
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_mix;

QUERY PLAN

Seq Scan on tbl_mix (actual rows=10000 loops=1)

Planning Time: 0.550 ms

Execution Time: 20.363 ms

EXPLAIN

-- plain 3
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array(arr)        FROM tbl_mix;

QUERY PLAN

Seq Scan on tbl_mix (actual rows=10000 loops=1)

Planning Time: 0.089 ms

Execution Time: 2.114 ms

EXPLAIN

-- strict 3
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF) SELECT f_sort_array_strict(arr) FROM tbl_mix;

QUERY PLAN

Seq Scan on tbl_mix (actual rows=10000 loops=1)

Planning Time: 0.042 ms

Execution Time: 18.632 ms

EXPLAIN