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
