PostgreSQL : compare two arrays, ignore ordering, PostgreSQL

PostgreSQL : compare two arrays, ignore ordering

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table arr(
  id  serial primary key,
  arr text[] not null
);

insert into arr(arr)
select     array_agg(case when random() > 0.1 then (random() * 15)::int::text end)
from       generate_series(1, 600) r
cross join generate_series(1, (random() * 10)::int) a
group by   r;

select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  a1.arr @> a2.arr
and    a1.arr <@ a2.arr;

explain analyze
select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  a1.arr @> a2.arr
and    a1.arr <@ a2.arr;

select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  not exists(select distinct unnest(a1.arr) except select distinct unnest(a2.arr))
and    not exists(select distinct unnest(a2.arr) except select distinct unnest(a1.arr));

explain analyze
select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  not exists(select distinct unnest(a1.arr) except select distinct unnest(a2.arr))
and    not exists(select distinct unnest(a2.arr) except select distinct unnest(a1.arr));

select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  (select array_agg(distinct e order by e) from unnest(a1.arr) e) = (select array_agg(distinct e order by e) from unnest(a2.arr) e);

explain analyze
select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  (select array_agg(distinct e order by e) from unnest(a1.arr) e) = (select array_agg(distinct e order by e) from unnest(a2.arr) e);

select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  array(select distinct e from unnest(a1.arr) e order by e) = array(select distinct e from unnest(a2.arr) e order by e);

explain analyze
select count(*)
from   arr a1
join   arr a2 on a1.id < a2.id
where  array(select distinct e from unnest(a1.arr) e order by e) = array(select distinct e from unnest(a2.arr) e order by e);


absolute service time: 8,32 sec

	count
1	8

	QUERY PLAN
1	Aggregate (cost=12531.08..12531.09 rows=1 width=0) (actual time=211.286..211.286 rows=1 loops=1)
2	-> Nested Loop (cost=0.28..12531.05 rows=13 width=0) (actual time=4.904..211.238 rows=8 loops=1)
3	-> Seq Scan on arr a1 (cost=0.00..22.70 rows=1270 width=36) (actual time=0.014..0.244 rows=600 loops=1)
4	-> Index Scan using arr_pkey on arr a2 (cost=0.28..9.84 rows=1 width=36) (actual time=0.346..0.350 rows=0 loops=600)
5	Index Cond: (a1.id < id)
6	Filter: ((a1.arr @> arr) AND (a1.arr <@ arr))
7	Rows Removed by Filter: 299
8	Planning time: 0.314 ms
9	Execution time: 211.386 ms

	count
1	26

	QUERY PLAN
1	Aggregate (cost=871292.26..871292.27 rows=1 width=0) (actual time=3646.796..3646.797 rows=1 loops=1)
2	-> Nested Loop (cost=0.28..870956.24 rows=134408 width=0) (actual time=35.374..3646.762 rows=26 loops=1)
3	-> Seq Scan on arr a1 (cost=0.00..22.70 rows=1270 width=36) (actual time=0.009..0.761 rows=600 loops=1)
4	-> Index Scan using arr_pkey on arr a2 (cost=0.28..684.71 rows=106 width=36) (actual time=5.908..6.074 rows=0 loops=600)
5	Index Cond: (a1.id < id)
6	Filter: ((NOT (SubPlan 1)) AND (NOT (SubPlan 2)))
7	Rows Removed by Filter: 299
8	SubPlan 1
9	-> HashSetOp Except (cost=0.76..5.03 rows=100 width=0) (actual time=0.018..0.018 rows=1 loops=179700)
10	-> Append (cost=0.76..4.53 rows=200 width=0) (actual time=0.006..0.015 rows=9 loops=179700)
11	-> Subquery Scan on "SELECT 1" (cost=0.76..2.26 rows=100 width=0) (actual time=0.006..0.007 rows=4 loops=179700)
12	-> HashAggregate (cost=0.76..1.26 rows=100 width=0) (actual time=0.004..0.005 rows=4 loops=179700)
13	Group Key: unnest(a1.arr)
14	-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.002 rows=5 loops=179700)
15	-> Subquery Scan on "SELECT 2" (cost=0.76..2.26 rows=100 width=0) (actual time=0.006..0.007 rows=4 loops=179700)
16	-> HashAggregate (cost=0.76..1.26 rows=100 width=0) (actual time=0.004..0.005 rows=4 loops=179700)
17	Group Key: unnest(a2.arr)
18	-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.002 rows=5 loops=179700)
19	SubPlan 2
20	-> HashSetOp Except (cost=0.76..5.03 rows=100 width=0) (actual time=0.023..0.023 rows=1 loops=377)
21	-> Append (cost=0.76..4.53 rows=200 width=0) (actual time=0.009..0.020 rows=8 loops=377)
22	-> Subquery Scan on "SELECT 1_1" (cost=0.76..2.26 rows=100 width=0) (actual time=0.008..0.009 rows=5 loops=377)
23	-> HashAggregate (cost=0.76..1.26 rows=100 width=0) (actual time=0.005..0.006 rows=5 loops=377)
24	Group Key: unnest(a2.arr)
25	-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.002..0.003 rows=5 loops=377)
26	-> Subquery Scan on "SELECT 2_1" (cost=0.76..2.26 rows=100 width=0) (actual time=0.008..0.009 rows=3 loops=377)
27	-> HashAggregate (cost=0.76..1.26 rows=100 width=0) (actual time=0.005..0.006 rows=3 loops=377)
28	Group Key: unnest(a1.arr)
29	-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.002..0.002 rows=5 loops=377)
30	Planning time: 0.413 ms
31	Execution time: 3647.091 ms

	count
1	26

	QUERY PLAN
1	Aggregate (cost=23948.10..23948.11 rows=1 width=0) (actual time=28.514..28.514 rows=1 loops=1)
2	-> Merge Join (cost=176.34..23941.38 rows=2688 width=0) (actual time=27.030..28.508 rows=26 loops=1)
3	Merge Cond: (((SubPlan 1)) = ((SubPlan 2)))
4	Join Filter: (a1.id < a2.id)
5	Rows Removed by Join Filter: 626
6	-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=14.318..14.343 rows=600 loops=1)
7	Sort Key: ((SubPlan 1))
8	Sort Method: quicksort Memory: 172kB
9	-> Seq Scan on arr a1 (cost=0.00..22.70 rows=1270 width=36) (actual time=0.062..10.510 rows=600 loops=1)
10	SubPlan 1
11	-> Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=600)
12	-> Function Scan on unnest e (cost=0.00..1.00 rows=100 width=32) (actual time=0.004..0.005 rows=5 loops=600)
13	-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=12.628..12.659 rows=652 loops=1)
14	Sort Key: ((SubPlan 2))
15	Sort Method: quicksort Memory: 172kB
16	-> Seq Scan on arr a2 (cost=0.00..22.70 rows=1270 width=36) (actual time=0.059..8.857 rows=600 loops=1)
17	SubPlan 2
18	-> Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=600)
19	-> Function Scan on unnest e_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.004..0.004 rows=5 loops=600)
20	Planning time: 0.375 ms
21	Execution time: 28.926 ms

	count
1	26

	QUERY PLAN
1	Aggregate (cost=90402.62..90402.63 rows=1 width=0) (actual time=24.847..24.847 rows=1 loops=1)
2	-> Merge Join (cost=176.34..90395.90 rows=2688 width=0) (actual time=23.304..24.836 rows=26 loops=1)
3	Merge Cond: (((SubPlan 1)) = ((SubPlan 2)))
4	Join Filter: (a1.id < a2.id)
5	Rows Removed by Join Filter: 626
6	-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=11.434..11.512 rows=600 loops=1)
7	Sort Key: ((SubPlan 1))
8	Sort Method: quicksort Memory: 172kB
9	-> Seq Scan on arr a1 (cost=0.00..22.70 rows=1270 width=36) (actual time=0.087..7.824 rows=600 loops=1)
10	SubPlan 1
11	-> Unique (cost=4.32..4.82 rows=100 width=32) (actual time=0.009..0.010 rows=4 loops=600)
12	-> Sort (cost=4.32..4.57 rows=100 width=32) (actual time=0.008..0.009 rows=5 loops=600)
13	Sort Key: e.e
14	Sort Method: quicksort Memory: 25kB
15	-> Function Scan on unnest e (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=5 loops=600)
16	-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=11.752..11.785 rows=652 loops=1)
17	Sort Key: ((SubPlan 2))
18	Sort Method: quicksort Memory: 172kB
19	-> Seq Scan on arr a2 (cost=0.00..22.70 rows=1270 width=36) (actual time=0.046..8.192 rows=600 loops=1)
20	SubPlan 2
21	-> Unique (cost=4.32..4.82 rows=100 width=32) (actual time=0.010..0.011 rows=4 loops=600)
22	-> Sort (cost=4.32..4.57 rows=100 width=32) (actual time=0.009..0.009 rows=5 loops=600)
23	Sort Key: e_1.e
24	Sort Method: quicksort Memory: 25kB
25	-> Function Scan on unnest e_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=5 loops=600)
26	Planning time: 0.349 ms
27	Execution time: 25.069 ms