Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mac_ip_addresses(
  id serial PRIMARY KEY
, name text NOT NULL DEFAULT 'some_dummy_for_the_test'
, ipaddress text[]
);

-- populate with arrays with roughly 5 elements, randomly distributed
INSERT INTO mac_ip_addresses (ipaddress)
SELECT array_agg(ip)
FROM  (
   SELECT *
        , count(step) OVER (ORDER BY random()) AS grp
   FROM  (
      SELECT format('192.168.%s.%s', g2, g1) AS ip
           , CASE WHEN random() > .8 THEN true ELSE NULL END AS step
      FROM   generate_series(1,255) g1
           , generate_series(1,95) g2
      ) sub
   ) sub
GROUP  BY grp;  -- 4781 rows

CREATE TABLE

INSERT 0 4802

-- custom operator
CREATE OR REPLACE FUNCTION reverse_like (text, text)
  RETURNS boolean LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT $2 LIKE $1';

CREATE OPERATOR <~~ (function = reverse_like, leftarg = text, rightarg = text);

CREATE FUNCTION

CREATE OPERATOR

TABLE mac_ip_addresses LIMIT 5;

id
name
ipaddress

1
some_dummy_for_the_test
{192.168.77.11,192.168.94.53,192.168.65.138,192.168.68.206,192.168.80.101,192.168.51.183}

2
some_dummy_for_the_test
{192.168.17.202}

3
some_dummy_for_the_test
{192.168.68.177,192.168.95.194}

4
some_dummy_for_the_test
{192.168.11.33,192.168.81.231}

5
some_dummy_for_the_test
{192.168.44.186,192.168.89.77,192.168.76.142,192.168.53.83,192.168.77.143,192.168.33.122,192.168.77.172,192.168.77.228}

SELECT 5

SELECT *
FROM   mac_ip_addresses
WHERE  '192.168.9%.255' <~~ ANY (ipaddress);

id
name
ipaddress

498
some_dummy_for_the_test
{192.168.75.224,192.168.43.20,192.168.92.255,192.168.13.204}

2649
some_dummy_for_the_test
{192.168.43.165,192.168.73.252,192.168.7.33,192.168.93.255,192.168.54.118,192.168.44.114}

2861
some_dummy_for_the_test
{192.168.20.98,192.168.9.255,192.168.46.78,192.168.41.127,192.168.44.141,192.168.25.215,192.168.50.179,192.168.9.88}

3229
some_dummy_for_the_test
{192.168.95.255,192.168.51.89,192.168.26.255,192.168.78.142,192.168.33.227}

3748
some_dummy_for_the_test
{192.168.6.51,192.168.44.148,192.168.72.232,192.168.89.219,192.168.60.238,192.168.63.58,192.168.94.255,192.168.49.55,192.168.58.248}

3981
some_dummy_for_the_test
{192.168.59.37,192.168.37.146,192.168.69.72,192.168.91.255,192.168.5.118,192.168.26.21,192.168.19.12,192.168.63.36}

4663
some_dummy_for_the_test
{192.168.66.17,192.168.63.35,192.168.90.255,192.168.75.64,192.168.17.227,192.168.64.190,192.168.47.103}

SELECT 7

-- compare
SELECT *
FROM   mac_ip_addresses
WHERE  EXISTS (SELECT FROM unnest(ipaddress) ip WHERE ip LIKE '192.168.9%.255');

id
name
ipaddress

498
some_dummy_for_the_test
{192.168.75.224,192.168.43.20,192.168.92.255,192.168.13.204}

2649
some_dummy_for_the_test
{192.168.43.165,192.168.73.252,192.168.7.33,192.168.93.255,192.168.54.118,192.168.44.114}

2861
some_dummy_for_the_test
{192.168.20.98,192.168.9.255,192.168.46.78,192.168.41.127,192.168.44.141,192.168.25.215,192.168.50.179,192.168.9.88}

3229
some_dummy_for_the_test
{192.168.95.255,192.168.51.89,192.168.26.255,192.168.78.142,192.168.33.227}

3748
some_dummy_for_the_test
{192.168.6.51,192.168.44.148,192.168.72.232,192.168.89.219,192.168.60.238,192.168.63.58,192.168.94.255,192.168.49.55,192.168.58.248}

3981
some_dummy_for_the_test
{192.168.59.37,192.168.37.146,192.168.69.72,192.168.91.255,192.168.5.118,192.168.26.21,192.168.19.12,192.168.63.36}

4663
some_dummy_for_the_test
{192.168.66.17,192.168.63.35,192.168.90.255,192.168.75.64,192.168.17.227,192.168.64.190,192.168.47.103}

SELECT 7

EXPLAIN ANALYZE
SELECT *
FROM   mac_ip_addresses
WHERE  '192.168.9%.255' <~~ ANY (ipaddress);

QUERY PLAN

Seq Scan on mac_ip_addresses  (cost=0.00..11674.80 rows=4590 width=68) (actual time=4.051..40.220 rows=7 loops=1)

  Filter: ('192.168.9%.255'::text <~~ ANY (ipaddress))

  Rows Removed by Filter: 4795

Planning Time: 0.033 ms

Execution Time: 40.241 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT *
FROM   mac_ip_addresses
WHERE  EXISTS (SELECT FROM unnest(ipaddress) ip WHERE ip LIKE '192.168.9%.255');

QUERY PLAN

Seq Scan on mac_ip_addresses  (cost=0.00..11697.75 rows=4590 width=68) (actual time=0.925..8.709 rows=7 loops=1)

  Filter: (SubPlan 1)

  Rows Removed by Filter: 4795

  SubPlan 1

    ->  Function Scan on unnest ip  (cost=0.00..1.25 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=4802)

          Filter: (ip ~~ '192.168.9%.255'::text)

          Rows Removed by Filter: 5

Planning Time: 0.085 ms

Execution Time: 8.728 ms

EXPLAIN

-- create special function & index
CREATE OR REPLACE FUNCTION f_textarr2text(text[]) 
  RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;
  
CREATE INDEX iparr_trigram_idx ON mac_ip_addresses
USING gin (f_textarr2text(ipaddress) gin_trgm_ops);

CREATE FUNCTION

CREATE INDEX

-- *much* faster
EXPLAIN ANALYZE
SELECT *
FROM   mac_ip_addresses
WHERE  NOT ('192.168.9%.255' <~~ ANY (ipaddress))
AND    f_textarr2text(ipaddress) LIKE '192.168.9%.255';  -- logically redundant

QUERY PLAN

Bitmap Heap Scan on mac_ip_addresses  (cost=100.00..105.52 rows=1 width=68) (actual time=1.159..1.160 rows=1 loops=1)

  Recheck Cond: (f_textarr2text(ipaddress) ~~ '192.168.9%.255'::text)

  Rows Removed by Index Recheck: 52

  Filter: (NOT ('192.168.9%.255'::text <~~ ANY (ipaddress)))

  Heap Blocks: exact=42

  ->  Bitmap Index Scan on iparr_trigram_idx  (cost=0.00..100.00 rows=1 width=0) (actual time=0.717..0.717 rows=53 loops=1)

        Index Cond: (f_textarr2text(ipaddress) ~~ '192.168.9%.255'::text)

Planning Time: 1.165 ms

Execution Time: 1.575 ms

EXPLAIN

-- still a bit faster, yet
EXPLAIN ANALYZE
SELECT *
FROM   mac_ip_addresses
WHERE  EXISTS (SELECT FROM unnest(ipaddress) ip WHERE ip LIKE '192.168.9%.255')
AND    f_textarr2text(ipaddress) LIKE '192.168.9%.255';  -- logically redundant

QUERY PLAN

Bitmap Heap Scan on mac_ip_addresses  (cost=100.00..105.52 rows=1 width=68) (actual time=0.666..0.667 rows=0 loops=1)

  Recheck Cond: (f_textarr2text(ipaddress) ~~ '192.168.9%.255'::text)

  Rows Removed by Index Recheck: 52

  Filter: (SubPlan 1)

  Rows Removed by Filter: 1

  Heap Blocks: exact=42

  ->  Bitmap Index Scan on iparr_trigram_idx  (cost=0.00..100.00 rows=1 width=0) (actual time=0.293..0.293 rows=53 loops=1)

        Index Cond: (f_textarr2text(ipaddress) ~~ '192.168.9%.255'::text)

  SubPlan 1

    ->  Function Scan on unnest ip  (cost=0.00..1.25 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)

          Filter: (ip ~~ '192.168.9%.255'::text)

          Rows Removed by Filter: 4

Planning Time: 0.107 ms

Execution Time: 0.692 ms

EXPLAIN

id	name	ipaddress
1	some_dummy_for_the_test	{192.168.77.11,192.168.94.53,192.168.65.138,192.168.68.206,192.168.80.101,192.168.51.183}
2	some_dummy_for_the_test	{192.168.17.202}
3	some_dummy_for_the_test	{192.168.68.177,192.168.95.194}
4	some_dummy_for_the_test	{192.168.11.33,192.168.81.231}
5	some_dummy_for_the_test	{192.168.44.186,192.168.89.77,192.168.76.142,192.168.53.83,192.168.77.143,192.168.33.122,192.168.77.172,192.168.77.228}

id	name	ipaddress
498	some_dummy_for_the_test	{192.168.75.224,192.168.43.20,192.168.92.255,192.168.13.204}
2649	some_dummy_for_the_test	{192.168.43.165,192.168.73.252,192.168.7.33,192.168.93.255,192.168.54.118,192.168.44.114}
2861	some_dummy_for_the_test	{192.168.20.98,192.168.9.255,192.168.46.78,192.168.41.127,192.168.44.141,192.168.25.215,192.168.50.179,192.168.9.88}
3229	some_dummy_for_the_test	{192.168.95.255,192.168.51.89,192.168.26.255,192.168.78.142,192.168.33.227}
3748	some_dummy_for_the_test	{192.168.6.51,192.168.44.148,192.168.72.232,192.168.89.219,192.168.60.238,192.168.63.58,192.168.94.255,192.168.49.55,192.168.58.248}
3981	some_dummy_for_the_test	{192.168.59.37,192.168.37.146,192.168.69.72,192.168.91.255,192.168.5.118,192.168.26.21,192.168.19.12,192.168.63.36}
4663	some_dummy_for_the_test	{192.168.66.17,192.168.63.35,192.168.90.255,192.168.75.64,192.168.17.227,192.168.64.190,192.168.47.103}