Tomasz Myrta <jasiek@klaster.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=1width=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
innerindexscans so that the setof usable joinclauses is determined accurately for each join. Formerly, the code only
consideredjoinclauses that used all of therels from the outer side of the join; thus for example FROM (aCROSS
JOINb) JOIN c ON (c.f1 = a.x AND c.f2 = b.y) could notexploit a two-column index on c(f1,f2), since neither of the
qualclauseswould be in the joininfo list it looked in. The new codedoes this correctly, and also is able to eliminate
redundantclauses,thus fixing the problem noted 24-Oct-02 by Hans-J�rgenSch�nig.
regards, tom lane