Postgres 14

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?.
-- *basically* equivalent (but keep reading ...)
SELECT idx('{1,2,3,7,8}', 7), array_position('{1,2,3,7,8}', 7);

idx
array_position

4
4

SELECT 1

-- not found?
SELECT idx('{1,2,3}', 4), array_position('{1,2,3}', 4);

idx
array_position

0
null

SELECT 1

-- null?
SELECT idx('{1,2,3,null}', 4);

ERROR:  array must not contain nulls

SELECT idx('{1,2,3}', null), array_position('{1,2,3, null}', null);

idx
array_position

null
4

SELECT 1

-- search with offset (not possible with idx())
SELECT array_position('{1,2,3,4,5,6, 3}', 3, 5);

array_position

7

SELECT 1

-- multidimensional array
SELECT idx('{{1,2,3},{4,5,6}}', 5);

idx

5

SELECT 1

SELECT array_position('{{1,2,3},{4,5,6}}', 5);

ERROR:  searching for elements in multidimensional arrays is not supported

-- non-standard array subscripts (results differ!)
SELECT idx('[5:7]={1,2,3}', 3), array_position('[5:7]={1,2,3}', 3);

idx
array_position

3
7

SELECT 1

-- performance with short arrays
CREATE TABLE arr AS
SELECT array_agg((random() * 100000)::int) AS a
FROM   generate_series(1, 1000000) g
GROUP  BY g/8;  -- 8 array elements

SELECT 125001

EXPLAIN (ANALYZE)
SELECT array_position(a, 12345) FROM arr;

QUERY PLAN

Seq Scan on arr  (cost=0.00..3480.30 rows=175304 width=4) (actual time=0.061..52.935 rows=125001 loops=1)

Planning Time: 0.098 ms

Execution Time: 57.581 ms

EXPLAIN

-- faster
EXPLAIN (ANALYZE)
SELECT idx(a, 12345) FROM arr;

QUERY PLAN

Seq Scan on arr  (cost=0.00..3480.30 rows=175304 width=4) (actual time=0.035..28.507 rows=125001 loops=1)

Planning Time: 0.048 ms

Execution Time: 33.157 ms

EXPLAIN

-- performance with long arrays
TRUNCATE arr;
INSERT INTO arr (a)
SELECT array_agg((random() * 100000)::int)
FROM   generate_series(1, 1000000) g
GROUP  BY g/150;  -- 150 array elements

TRUNCATE TABLE

INSERT 0 6667

EXPLAIN (ANALYZE)
SELECT array_position(a, 12345) FROM arr;

QUERY PLAN

Seq Scan on arr  (cost=0.00..1501.20 rows=75616 width=4) (actual time=0.019..14.073 rows=6667 loops=1)

Planning Time: 0.076 ms

Execution Time: 14.333 ms

EXPLAIN

-- *much* faster
EXPLAIN (ANALYZE)
SELECT idx(a, 12345) FROM arr;

QUERY PLAN

Seq Scan on arr  (cost=0.00..1501.20 rows=75616 width=4) (actual time=0.010..1.902 rows=6667 loops=1)

Planning Time: 0.032 ms

Execution Time: 2.157 ms

EXPLAIN