Are full outer joins expected to perform much worse than inner joins?
I'm seeing 2 orders of magnitude difference for an almost identical
query. (Well, as "identical" as you can get, comparing a query with an
outer join to one without.) This is on 8.0.3, recently analyzed. Here
are the explain plans:
music=# explain select
music-# extractbasenamefrompath(files.path),trms.trm
music-# from files,nodes
music-# full outer join trms on (trms.id = nodes.trm)
music-# where
music-# nodes.fileid = files.id and
music-# extractbasenamefrompath(files.path) = 'Joy Division/Substance/';
QUERY PLAN
---------------------------------------------------------------------------------------------
Hash Join (cost=3932.55..11891.05 rows=18 width=117)
Hash Cond: ("outer".fileid = "inner".id)
-> Hash Left Join (cost=3867.51..11391.65 rows=86827 width=44)
Hash Cond: ("outer".trm = "inner".id)
-> Seq Scan on nodes (cost=0.00..1557.27 rows=86827 width=8)
-> Hash (cost=2867.21..2867.21 rows=88521 width=44)
-> Seq Scan on trms (cost=0.00..2867.21 rows=88521
width=44)
-> Hash (cost=64.99..64.99 rows=18 width=81)
-> Index Scan using basename_idx on files (cost=0.00..64.99
rows=18 width=81)
Index Cond: (extractbasenamefrompath(path) = 'Joy
Division/Substance/'::text)
(10 rows)
music=# explain select
music-# extractbasenamefrompath(files.path),trms.trm
music-# from
music-# nodes,files,trms
music-# where
music-# nodes.fileid = files.id and
music-# nodes.trm = trms.id and
music-# extractbasenamefrompath(files.path) = 'Joy Division/Substance/';
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..202.04 rows=18 width=117)
-> Nested Loop (cost=0.00..119.46 rows=18 width=81)
-> Index Scan using basename_idx on files (cost=0.00..64.99
rows=18 width=81)
Index Cond: (extractbasenamefrompath(path) = 'Joy
Division/Substance/'::text)
-> Index Scan using nodes_fileid_idx on nodes
(cost=0.00..3.01 rows=1 width=8)
Index Cond: (nodes.fileid = "outer".id)
-> Index Scan using trms_pkey on trms (cost=0.00..4.57 rows=1 width=44)
Index Cond: ("outer".trm = trms.id)
(8 rows)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
