Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table groups (PostCodeFootPrint int[]);
insert into groups (PostCodeFootPrint) values
('{2529,2530,2533,2534,2535,2536,2537,2538,2539,2540,2541,2575,2576,2577,2580}'),
('{2640}'),
('{3844}'),
('{2063, 2064, 2065, 2066, 2067, 2068, 2069, 2070, 2071, 2072, 2073, 2074, 2075, 2076, 2077, 2079, 2080, 2081, 2082, 2083, 2119, 2120, 2126, 2158, 2159}'),
('{2848, 2849, 2850, 2852}');

create table au_postcode (poa_code int);
insert into au_postcode (poa_code) values 
(2090),(2092),(2093),(829),(830),(836),(2080),(2081);

create table groups2 (which varchar, footprint int);
insert into groups2 (which, footprint) values
('group1', 2529), ('group1', 2530), ('group1', 2533), ('group1', 2534), ('group1', 2535), ('group1', 2536),
('group1', 2537), ('group1', 2538), ('group1', 2539), ('group1', 2540), ('group1', 2541), ('group1', 2575),
('group1', 2576), ('group1', 2577), ('group1', 2580), 
('group2', 2640), ('group3', 3844),
('group4', 2063), ('group4', 2064), ('group4', 2065), ('group4', 2066), ('group4', 2067), ('group4', 2068),
('group4', 2069), ('group4', 2070), ('group4', 2071), ('group4', 2072), ('group4', 2073), ('group4', 2074),
('group4', 2075), ('group4', 2076), ('group4', 2077), ('group4', 2079), ('group4', 2080), ('group4', 2081),
('group4', 2082), ('group4', 2083), ('group4', 2119), ('group4', 2120), ('group4', 2126), ('group4', 2158), ('group4', 2159),
('group5', 2848), ('group5', 2849), ('group5', 2850), ('group5', 2852);
5 rows affected

8 rows affected

46 rows affected

select p.poa_code, g.which
from groups2 g
join au_postcode p 
on g.footprint = p.poa_code;


poa_code
which


2080
group4

2081
group4


explain analyze
select p.poa_code, g.which
from groups2 g
join au_postcode p 
on g.footprint = p.poa_code;


QUERY PLAN


Merge Join  (cost=267.95..517.20 rows=16192 width=36) (actual time=0.052..0.054 rows=2 loops=1)

  Merge Cond: (g.footprint = p.poa_code)

  ->  Sort  (cost=88.17..91.35 rows=1270 width=36) (actual time=0.030..0.031 rows=21 loops=1)

        Sort Key: g.footprint

        Sort Method: quicksort  Memory: 27kB

        ->  Seq Scan on groups2 g  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.017..0.020 rows=46 loops=1)

  ->  Sort  (cost=179.78..186.16 rows=2550 width=4) (actual time=0.018..0.018 rows=8 loops=1)

        Sort Key: p.poa_code

        Sort Method: quicksort  Memory: 25kB

        ->  Seq Scan on au_postcode p  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.005..0.005 rows=8 loops=1)

Planning Time: 0.048 ms

Execution Time: 0.070 ms


explain analyze
select p.poa_code, g.PostCodeFootPrint
from groups g
join au_postcode p 
on (g.PostCodeFootPrint # p.poa_code) > 0;


QUERY PLAN


Nested Loop  (cost=0.00..60752.50 rows=1156000 width=36) (actual time=0.030..0.033 rows=2 loops=1)

  Join Filter: ((g.postcodefootprint # p.poa_code) > 0)

  Rows Removed by Join Filter: 38

  ->  Seq Scan on au_postcode p  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.007..0.007 rows=8 loops=1)

  ->  Materialize  (cost=0.00..30.40 rows=1360 width=32) (actual time=0.001..0.002 rows=5 loops=8)

        ->  Seq Scan on groups g  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.006..0.007 rows=5 loops=1)

Planning Time: 0.066 ms

Execution Time: 0.487 ms