Example: Join on a non-indexed attribute <~>(2016)
uni=# explain analyze
uni=# select s1.code, s2.code
uni-# from Subjects s1, Subjects s2
uni=# where s1.offeredBy=s2.offeredBy;
QUERY PLAN
---------------------------------------------------------------
Merge Join (cost=4449.13..121322.06 rows=7785262 width=18)
(actual time=29.787..2377.707 rows=8039979 loops=1)
Merge Cond: (s1.offeredby = s2.offeredby)
-> Sort (cost=2224.57..2271.56 rows=18799 width=13)
(actual time=14.251..18.703 rows=18570 loops=1)
Sort Key: s1.offeredby
Sort Method: external merge Disk: 472kB
-> Seq Scan on subjects s1
(cost=0.00..889.99 rows=18799 width=13)
(actual time=0.005..4.542 rows=18799 loops=1)
-> Sort (cost=2224.57..2271.56 rows=18799 width=13)
(actual time=15.532..1100.396 rows=8039980 loops=1)
Sort Key: s2.offeredby
Sort Method: external sort Disk: 552kB
-> Seq Scan on subjects s2
(cost=0.00..889.99 rows=18799 width=13)
(actual time=0.002..3.579 rows=18799 loops=1)
Total runtime: 2767.1 ms
Example: Join on a non-indexed attribute <~>(2018)
uni=# explain analyze
uni=# select s1.code, s2.code
uni-# from Subjects s1, Subjects s2
uni-# where s1.offeredBy = s2.offeredBy;
QUERY PLAN
---------------------------------------------------------------
Hash Join (cost=1286.03..108351.87 rows=7113299 width=18)
(actual time=8.966..903.441 rows=7328594 loops=1)
Hash Cond: (s1.offeredby = s2.offeredby)
-> Seq Scan on subjects s1
(cost=0.00..1063.79 rows=17779 width=13)
(actual time=0.013..2.861 rows=17779 loops=1)
-> Hash (cost=1063.79..1063.79 rows=17779 width=13)
(actual time=8.667..8.667 rows=17720 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1087kB
-> Seq Scan on subjects s2
(cost=0.00..1063.79 rows=17779 width=13)
(actual time=0.009..4.677 rows=17779 loops=1)
Planning time: 0.255 ms
Execution time: 1191.023 ms
Example: Join on a non-indexed attribute <~>(2018)
uni=# explain analyze
uni=# select s1.code, s2.code
uni-# from Subjects s1, Subjects s2
uni-# where s1.offeredBy = s2.offeredBy and s1.code < s2.code;
QUERY PLAN
---------------------------------------------------------------
Hash Join (cost=1286.03..126135.12 rows=2371100 width=18)
(actual time=7.356..6806.042 rows=3655437 loops=1)
Hash Cond: (s1.offeredby = s2.offeredby)
Join Filter: (s1.code < s2.code)
Rows Removed by Join Filter: 3673157
-> Seq Scan on subjects s1
(cost=0.00..1063.79 rows=17779 width=13)
(actual time=0.009..4.602 rows=17779 loops=1)
-> Hash (cost=1063.79..1063.79 rows=17779 width=13)
(actual time=7.301..7.301 rows=17720 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1087kB
-> Seq Scan on subjects s2
(cost=0.00..1063.79 rows=17779 width=13)
(actual time=0.005..4.452 rows=17779 loops=1)
Planning time: 0.159 ms
Execution time: 6949.167 ms