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.065..57.295 rows=125001 loops=1) |
| Planning Time: 0.278 ms |
| Execution Time: 61.874 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.050..29.043 rows=125001 loops=1) |
| Planning Time: 0.050 ms |
| Execution Time: 33.611 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..13.944 rows=6667 loops=1) |
| Planning Time: 0.077 ms |
| Execution Time: 14.211 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.012..2.270 rows=6667 loops=1) |
| Planning Time: 0.048 ms |
| Execution Time: 2.544 ms |
EXPLAIN