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 arr::text
, -1 = ALL(arr) IS NULL AS xsimple
, 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL AS simple
, array_remove(arr, NULL) = '{}' AS array_rem
, cardinality(array_positions(arr, NULL))
= cardinality(arr) AS array_pos
, TRUE = ALL (SELECT unnest(arr) IS NULL) AS michael
, (SELECT bool_and(e IS NULL) FROM unnest(arr) e) AS bool_and
, NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS exist
FROM (
VALUES
('{1,2,NULL,3}'::int[])
, ('{1,1,1}')
, ('{2,2,2}')
, ('{NULL,NULL,NULL}')
, ('{}'::int[])
) t(arr);
| arr | xsimple | simple | array_rem | array_pos | michael | bool_and | exist |
|---|---|---|---|---|---|---|---|
| {1,2,NULL,3} | f | f | f | f | f | f | f |
| {1,1,1} | f | f | f | f | f | f | f |
| {2,2,2} | f | f | f | f | f | f | f |
| {NULL,NULL,NULL} | t | t | t | t | t | t | t |
| {} | f | f | t | t | t | null | t |
-- Performance
-- table with short arrays (40000 rows á 3-4 elements)
CREATE TABLE t1 AS
SELECT ARRAY[i,i,NULL,i] AS arr FROM generate_series(1, 10000) i
UNION ALL
SELECT ARRAY[1,1,1] FROM generate_series(1, 10000) i
UNION ALL
SELECT ARRAY[2,2,2] FROM generate_series(1, 10000) i
UNION ALL
SELECT ARRAY[NULL,NULL,NULL]::int[] FROM generate_series(1, 10000) i;
-- table with long arrays (~ 10000 rows á ~ 100 elements)
CREATE TABLE t2 AS
SELECT array_agg(CASE WHEN random() > 0.1 THEN NULL ELSE (random()* 100000)::int END) AS arr
FROM generate_series(1, 1000000) i
GROUP BY (random()* 10000)::int;
40000 rows affected
10001 rows affected
VACUUM ANALYZE t1;
VACUUM ANALYZE t2;
-- prewarm cache (and show counts)
SELECT count(*), count(arr), avg(cardinality(arr)) FROM t1;
SELECT count(*), count(arr), avg(cardinality(arr)) FROM t2;
| count | count | avg |
|---|---|---|
| 40000 | 40000 | 3.2500000000000000 |
| count | count | avg |
|---|---|---|
| 10001 | 10001 | 99.9900009999000100 |
-- xsimple
EXPLAIN (ANALYZE, TIMING OFF)
SELECT -1 = ALL(arr) IS NULL AS xsimple FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..1234.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| Planning time: 0.018 ms |
| Execution time: 6.934 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT -1 = ALL(arr) IS NULL AS xsimple FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..361.02 rows=10001 width=77) (actual rows=10001 loops=1) |
| Planning time: 0.017 ms |
| Execution time: 1.896 ms |
-- simple
EXPLAIN (ANALYZE, TIMING OFF)
SELECT 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL AS simple FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..1734.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| Planning time: 0.017 ms |
| Execution time: 7.778 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT 1 = ALL(arr) IS NULL AND 2 = ALL(arr) IS NULL AS simple FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..486.03 rows=10001 width=77) (actual rows=10001 loops=1) |
| Planning time: 0.023 ms |
| Execution time: 2.010 ms |
-- array_rem
EXPLAIN (ANALYZE, TIMING OFF)
SELECT array_remove(arr, NULL) = '{}' AS array_rem FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..934.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| Planning time: 0.029 ms |
| Execution time: 10.749 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT array_remove(arr, NULL) = '{}' AS array_rem FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..286.01 rows=10001 width=77) (actual rows=10001 loops=1) |
| Planning time: 0.017 ms |
| Execution time: 8.786 ms |
-- array_pos
EXPLAIN (ANALYZE, TIMING OFF)
SELECT cardinality(array_positions(arr, NULL)) = cardinality(arr) AS array_pos FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..1134.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| Planning time: 0.018 ms |
| Execution time: 18.284 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT cardinality(array_positions(arr, NULL)) = cardinality(arr) AS array_pos FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..336.02 rows=10001 width=77) (actual rows=10001 loops=1) |
| Planning time: 0.020 ms |
| Execution time: 26.493 ms |
-- michael
EXPLAIN (ANALYZE, TIMING OFF)
SELECT TRUE = ALL (SELECT unnest(arr) IS NULL) AS michael FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..15884.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| SubPlan 1 |
| -> Result (cost=0.00..0.51 rows=100 width=0) (actual rows=2 loops=40000) |
| Planning time: 0.035 ms |
| Execution time: 23.441 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT TRUE = ALL (SELECT unnest(arr) IS NULL) AS michael FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..4023.89 rows=10001 width=77) (actual rows=10001 loops=1) |
| SubPlan 1 |
| -> Result (cost=0.00..0.51 rows=100 width=0) (actual rows=10 loops=10001) |
| Planning time: 0.047 ms |
| Execution time: 12.715 ms |
-- bool_and
EXPLAIN (ANALYZE, TIMING OFF)
SELECT (SELECT bool_and(e IS NULL) FROM unnest(arr) e) AS bool_and FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..51234.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| SubPlan 1 |
| -> Aggregate (cost=1.25..1.26 rows=1 width=4) (actual rows=1 loops=40000) |
| -> Function Scan on unnest e (cost=0.00..1.00 rows=100 width=4) (actual rows=3 loops=40000) |
| Planning time: 0.042 ms |
| Execution time: 88.236 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT (SELECT bool_and(e IS NULL) FROM unnest(arr) e) AS bool_and FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..12862.27 rows=10001 width=77) (actual rows=10001 loops=1) |
| SubPlan 1 |
| -> Aggregate (cost=1.25..1.26 rows=1 width=4) (actual rows=1 loops=10001) |
| -> Function Scan on unnest e (cost=0.00..1.00 rows=100 width=4) (actual rows=100 loops=10001) |
| Planning time: 0.059 ms |
| Execution time: 186.554 ms |
-- exist
EXPLAIN (ANALYZE, TIMING OFF)
SELECT NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS exist FROM t1;
| QUERY PLAN |
|---|
| Seq Scan on t1 (cost=0.00..71934.00 rows=40000 width=34) (actual rows=40000 loops=1) |
| SubPlan 1 |
| -> HashSetOp Except (cost=0.00..1.78 rows=1 width=0) (actual rows=1 loops=40000) |
| -> Append (cost=0.00..1.53 rows=101 width=0) (actual rows=4 loops=40000) |
| -> Subquery Scan on "*SELECT* 1" (cost=0.00..1.51 rows=100 width=0) (actual rows=3 loops=40000) |
| -> Result (cost=0.00..0.51 rows=100 width=0) (actual rows=3 loops=40000) |
| -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual rows=1 loops=40000) |
| -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=40000) |
| Planning time: 0.066 ms |
| Execution time: 102.282 ms |
EXPLAIN (ANALYZE, TIMING OFF)
SELECT NOT EXISTS (SELECT unnest(arr) EXCEPT SELECT null) AS exist FROM t2;
| QUERY PLAN |
|---|
| Seq Scan on t2 (cost=0.00..18037.79 rows=10001 width=77) (actual rows=10001 loops=1) |
| SubPlan 1 |
| -> HashSetOp Except (cost=0.00..1.78 rows=1 width=0) (actual rows=1 loops=10001) |
| -> Append (cost=0.00..1.53 rows=101 width=0) (actual rows=101 loops=10001) |
| -> Subquery Scan on "*SELECT* 1" (cost=0.00..1.51 rows=100 width=0) (actual rows=100 loops=10001) |
| -> Result (cost=0.00..0.51 rows=100 width=0) (actual rows=100 loops=10001) |
| -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual rows=1 loops=10001) |
| -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=10001) |
| Planning time: 0.063 ms |
| Execution time: 213.559 ms |