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