From: | Ben <bench(at)silentmedia(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | full outer join performance |
Date: | 2005-09-13 17:36:00 |
Message-ID: | 43270E00.9000503@silentmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-09-13 18:12:28 | Re: full outer join performance |
Previous Message | Celia McInnis | 2005-09-13 17:05:50 | Using COPY command when input file contain backslashes? |