full outer join performance
От | Ben |
---|---|
Тема | full outer join performance |
Дата | |
Msg-id | 43270E00.9000503@silentmedia.com обсуждение исходный текст |
Ответы |
Re: full outer join performance
|
Список | 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)
В списке pgsql-general по дате отправления: