Postgres 11

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?.

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
4742 rows affected

-- 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);
TABLE mac_ip_addresses LIMIT 5;


id
name
ipaddress


1
some_dummy_for_the_test
{192.168.84.184}

2
some_dummy_for_the_test
{192.168.47.78,192.168.10.147}

3
some_dummy_for_the_test
{192.168.55.193}

4
some_dummy_for_the_test
{192.168.16.52,192.168.28.221,192.168.61.225}

5
some_dummy_for_the_test
{192.168.61.104,192.168.74.143,192.168.17.136}


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


id
name
ipaddress


1195
some_dummy_for_the_test
{192.168.92.255,192.168.61.190}

2036
some_dummy_for_the_test
{192.168.55.66,192.168.51.1,192.168.90.255}

2295
some_dummy_for_the_test
{192.168.93.255,192.168.76.30,192.168.92.23,192.168.74.75,192.168.62.100,192.168.87.192,192.168.80.221,192.168.1.198,192.168.51.152,192.168.83.231,192.168.94.104,192.168.16.199}

3733
some_dummy_for_the_test
{192.168.93.32,192.168.88.44,192.168.65.211,192.168.93.251,192.168.20.67,192.168.91.11,192.168.86.89,192.168.91.255,192.168.9.137,192.168.78.136,192.168.77.205}

3773
some_dummy_for_the_test
{192.168.64.250,192.168.50.106,192.168.67.45,192.168.94.255,192.168.61.131,192.168.73.67,192.168.37.166,192.168.15.2,192.168.94.144,192.168.4.171,192.168.54.178,192.168.9.160}

4036
some_dummy_for_the_test
{192.168.39.39,192.168.77.145,192.168.19.32,192.168.24.224,192.168.79.248,192.168.9.255,192.168.10.157}

4649
some_dummy_for_the_test
{192.168.95.255,192.168.50.173,192.168.8.30,192.168.22.120,192.168.17.242,192.168.61.224,192.168.2.233,192.168.20.25}


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


id
name
ipaddress


1195
some_dummy_for_the_test
{192.168.92.255,192.168.61.190}

2036
some_dummy_for_the_test
{192.168.55.66,192.168.51.1,192.168.90.255}

2295
some_dummy_for_the_test
{192.168.93.255,192.168.76.30,192.168.92.23,192.168.74.75,192.168.62.100,192.168.87.192,192.168.80.221,192.168.1.198,192.168.51.152,192.168.83.231,192.168.94.104,192.168.16.199}

3733
some_dummy_for_the_test
{192.168.93.32,192.168.88.44,192.168.65.211,192.168.93.251,192.168.20.67,192.168.91.11,192.168.86.89,192.168.91.255,192.168.9.137,192.168.78.136,192.168.77.205}

3773
some_dummy_for_the_test
{192.168.64.250,192.168.50.106,192.168.67.45,192.168.94.255,192.168.61.131,192.168.73.67,192.168.37.166,192.168.15.2,192.168.94.144,192.168.4.171,192.168.54.178,192.168.9.160}

4036
some_dummy_for_the_test
{192.168.39.39,192.168.77.145,192.168.19.32,192.168.24.224,192.168.79.248,192.168.9.255,192.168.10.157}

4649
some_dummy_for_the_test
{192.168.95.255,192.168.50.173,192.168.8.30,192.168.22.120,192.168.17.242,192.168.61.224,192.168.2.233,192.168.20.25}


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..11566.70 rows=4548 width=68) (actual time=12.144..46.191 rows=7 loops=1)

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

  Rows Removed by Filter: 4735

Planning Time: 0.029 ms

Execution Time: 46.208 ms


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..11589.44 rows=4548 width=68) (actual time=2.348..9.394 rows=7 loops=1)

  Filter: (SubPlan 1)

  Rows Removed by Filter: 4735

  SubPlan 1

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

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

          Rows Removed by Filter: 5

Planning Time: 0.045 ms

Execution Time: 9.407 ms


-- 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);
-- *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=0.730..0.730 rows=0 loops=1)

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

  Rows Removed by Index Recheck: 47

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

  Heap Blocks: exact=37

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

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

Planning Time: 0.264 ms

Execution Time: 0.760 ms


-- 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.625..0.625 rows=0 loops=1)

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

  Rows Removed by Index Recheck: 47

  Filter: (SubPlan 1)

  Heap Blocks: exact=37

  ->  Bitmap Index Scan on iparr_trigram_idx  (cost=0.00..100.00 rows=1 width=0) (actual time=0.278..0.278 rows=47 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) (never executed)

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

Planning Time: 0.102 ms

Execution Time: 0.650 ms

id	name	ipaddress
1	some_dummy_for_the_test	{192.168.84.184}
2	some_dummy_for_the_test	{192.168.47.78,192.168.10.147}
3	some_dummy_for_the_test	{192.168.55.193}
4	some_dummy_for_the_test	{192.168.16.52,192.168.28.221,192.168.61.225}
5	some_dummy_for_the_test	{192.168.61.104,192.168.74.143,192.168.17.136}

id	name	ipaddress
1195	some_dummy_for_the_test	{192.168.92.255,192.168.61.190}
2036	some_dummy_for_the_test	{192.168.55.66,192.168.51.1,192.168.90.255}
2295	some_dummy_for_the_test	{192.168.93.255,192.168.76.30,192.168.92.23,192.168.74.75,192.168.62.100,192.168.87.192,192.168.80.221,192.168.1.198,192.168.51.152,192.168.83.231,192.168.94.104,192.168.16.199}
3733	some_dummy_for_the_test	{192.168.93.32,192.168.88.44,192.168.65.211,192.168.93.251,192.168.20.67,192.168.91.11,192.168.86.89,192.168.91.255,192.168.9.137,192.168.78.136,192.168.77.205}
3773	some_dummy_for_the_test	{192.168.64.250,192.168.50.106,192.168.67.45,192.168.94.255,192.168.61.131,192.168.73.67,192.168.37.166,192.168.15.2,192.168.94.144,192.168.4.171,192.168.54.178,192.168.9.160}
4036	some_dummy_for_the_test	{192.168.39.39,192.168.77.145,192.168.19.32,192.168.24.224,192.168.79.248,192.168.9.255,192.168.10.157}
4649	some_dummy_for_the_test	{192.168.95.255,192.168.50.173,192.168.8.30,192.168.22.120,192.168.17.242,192.168.61.224,192.168.2.233,192.168.20.25}