Re: explicit joins wrong planning

Tomasz Myrta <jasiek(at)klaster(dot)net> writes:
> It's much better now (10x faster), but I've just found this plan still
> isn't as I want to have. I wish I could have index usage on both fields,

FWIW, I see this plan in 7.4 using your original test case:

Nested Loop (cost=22.51..71.79 rows=1 width=56)
-> Hash Join (cost=22.51..47.56 rows=5 width=48)
Hash Cond: ("outer".groupid = "inner".groupid)
-> Seq Scan on offsets o (cost=0.00..20.00 rows=1000 width=12)
-> Hash (cost=22.50..22.50 rows=5 width=40)
-> Seq Scan on groups g (cost=0.00..22.50 rows=5 width=40)
Filter: ((name)::text = 'some_name'::text)
-> Index Scan using events_pkey on events e (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((e.offset_id = "outer".offset_id) AND (e.event_date = ("outer".begindate + "outer".offset_value)))

I believe the difficulty in 7.3 is because begindate and offset_value
come from different relations, and specifically from different relations
than the first index condition uses. This was fixed here:

2002-11-24 16:52 tgl

* src/: backend/nodes/copyfuncs.c, backend/nodes/equalfuncs.c,
backend/nodes/list.c, backend/nodes/outfuncs.c,
backend/nodes/readfuncs.c, backend/optimizer/path/indxpath.c,
backend/optimizer/path/joinpath.c,
backend/optimizer/path/orindxpath.c,
backend/optimizer/path/tidpath.c,
backend/optimizer/plan/initsplan.c,
backend/optimizer/util/pathnode.c,
backend/optimizer/util/plancat.c, backend/optimizer/util/relnode.c,
backend/optimizer/util/restrictinfo.c, include/nodes/nodes.h,
include/nodes/pg_list.h, include/nodes/relation.h,
include/optimizer/paths.h, include/optimizer/restrictinfo.h:
Restructure planning of nestloop inner indexscans so that the set
of usable joinclauses is determined accurately for each join.
Formerly, the code only considered joinclauses that used all of the
rels from the outer side of the join; thus for example FROM (a
CROSS JOIN b) JOIN c ON (c.f1 = a.x AND c.f2 = b.y) could not
exploit a two-column index on c(f1,f2), since neither of the qual
clauses would be in the joininfo list it looked in. The new code
does this correctly, and also is able to eliminate redundant
clauses, thus fixing the problem noted 24-Oct-02 by Hans-Jrgen
Schnig.

regards, tom lane