Waiting for 9.5 – array_offset() and array_offsets()

On 18th of March, Alvaro Herrera committed patch:

array_offset() and array_offsets()
 
These functions return the offset position or positions of a value in an
array.
 
Author: Pavel Stěhule
Reviewed by: Jim Nasby

It's been a while since my last “waiting for" post – mostly because while there is a lot of work happening, a lot of optimizations, bugfixes and new functionalities in the PostgreSQL, not all that much is easily visible from SQL level.

So, today we got two new functions. I was missing them for a very long time, and never really understood how come we didn't have them. But now, we finally do.

Basically, these are for finding at what position given value is in array, like:

SELECT array_offset( '{a,b,c,d,s,a,c,a,r,z}'::text[], 'a' );
 array_offset 
--------------
            1
(1 ROW)
 
SELECT array_offsets( '{a,b,c,d,s,a,c,a,r,z}'::text[], 'a' );
 array_offsets 
---------------
 {1,6,8}
(1 ROW)

The difference seems to be obvious, usecases too.

A word of warning, though. In various programs it's pretty common to test for existence of value in array by checking its position.

So one could be tempted to use array_offset(s) to test if given value exists in the array. Which is terrible mistake, as this is not indexable operation:

$ CREATE TABLE test (id serial PRIMARY KEY, some_values int4[] );
CREATE TABLE
 
$ INSERT INTO test (some_values) SELECT array[ (random()*1000)::int4, (random()*1000)::int4, (random()*1000)::int4, (random()*1000)::int4, (random()*1000)::int4] FROM generate_series(1,1000000);
INSERT 0 1000000
 
$ CREATE INDEX test_idx ON test USING gin (some_values);
CREATE INDEX
 
$ EXPLAIN analyze SELECT * FROM test WHERE array_offset(some_values, 123) IS NOT NULL;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan ON test  (cost=0.00..21846.12 ROWS=995010 width=45) (actual TIME=0.199..178.924 ROWS=4924 loops=1)
   FILTER: (array_offset(some_values, 123) IS NOT NULL)
   ROWS Removed BY FILTER: 995086
 Planning TIME: 0.380 ms
 Execution TIME: 179.111 ms
(5 ROWS)

Instead, what you should do is use proper array operators for finding the rows, and only then extract the position, if you need it:

$ EXPLAIN analyze SELECT *, array_offset(some_values, 123) FROM test WHERE some_values @> '{123}'::int4[];
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan ON test  (cost=52.43..8003.66 ROWS=4700 width=45) (actual TIME=1.607..13.749 ROWS=4924 loops=1)
   Recheck Cond: (some_values @> '{123}'::INTEGER[])
   Heap Blocks: exact=3825
   ->  Bitmap INDEX Scan ON test_idx  (cost=0.00..51.25 ROWS=4700 width=0) (actual TIME=0.989..0.989 ROWS=4924 loops=1)
         INDEX Cond: (some_values @> '{123}'::INTEGER[])
 Planning TIME: 0.433 ms
 Execution TIME: 14.029 ms
(7 ROWS)

Looks pretty useful, and I'll definitely will be playing with it.

One thought on “Waiting for 9.5 – array_offset() and array_offsets()”

Comments are closed.