apply outer->inner join optimisation to OR clauses
От | Bradley Baetz |
---|---|
Тема | apply outer->inner join optimisation to OR clauses |
Дата | |
Msg-id | 20030503072813.GA12777@mango.home обсуждение исходный текст |
Ответы |
Re: apply outer->inner join optimisation to OR clauses
|
Список | pgsql-patches |
The attached patch applies the optimisation translating outer joins to inner joins (where safe) to the cases where the WHERE clause has OR bits in it too, if the column is present (and not null) in all of the OR bits. This allows, for example: bbaetz=# explain analyze select bugs.bug_id from bugs left join longdescs using (bug_id) where who IN (1,2); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Merge Left Join (cost=149629.84..172021.09 rows=100000 width=12) (actual time=20102.71..23694.30 rows=105 loops=1) Merge Cond: ("outer".bug_id = "inner".bug_id) Filter: (("inner".who = 1) OR ("inner".who = 2)) -> Index Scan using bugs_pkey on bugs (cost=0.00..2142.00 rows=100000 width=4) (actual time=6.17..310.94 rows=100000 loops=1) -> Sort (cost=149629.84..152129.84 rows=1000000 width=8) (actual time=19969.66..21317.62 rows=1000000 loops=1) Sort Key: longdescs.bug_id -> Seq Scan on longdescs (cost=0.00..14902.00 rows=1000000 width=8) (actual time=0.03..4225.04 rows=1000000 loops=1) Total runtime: 23739.90 msec (8 rows) to become: bbaetz=# explain analyze select bugs.bug_id from bugs left join longdescs using (bug_id) where who IN (1,2); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..691.64 rows=99 width=8) (actual time=42.54..1289.34 rows=105 loops=1) -> Index Scan using longdescs_who_idx, longdescs_who_idx on longdescs (cost=0.00..395.09 rows=98 width=4) (actual time=7.31..547.09 rows=105 loops=1) Index Cond: ((who = 1) OR (who = 2)) -> Index Scan using bugs_pkey on bugs (cost=0.00..3.01 rows=1 width=4) (actual time=7.06..7.06 rows=1 loops=105) Index Cond: (bugs.bug_id = "outer".bug_id) Total runtime: 1289.60 msec (6 rows) I wanted to add a regression test, but it doesn't look like theres infrastructure to test that an optimisation is being applied. Thanks, Bradley
Вложения
В списке pgsql-patches по дате отправления: