apply outer->inner join optimisation to OR clauses

From: Bradley Baetz <bbaetz(at)acm(dot)org>
To: pgsql-patches(at)postgresql(dot)org
Subject: apply outer->inner join optimisation to OR clauses
Date: 2003-05-03 07:28:13
Message-ID: 20030503072813.GA12777@mango.home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
orJoin.patch text/plain 2.3 KB

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message Joe Conway 2003-05-03 15:06:57 contrib/tablefunc bugfix
Previous Message Bruce Momjian 2003-05-03 05:13:51 Cleanup for Win32