From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: explicit joins wrong planning
Date: Fri, 28 Nov 2003 16:57:18 -0500
Message-ID: <1517.1070056638@sss.pgh.pa.us> (raw)
In-Reply-To: <3FC7AC6F.7080603@klaster.net>
References: <3FC63057.3080009@klaster.net>
<29784.1070047126@sss.pgh.pa.us>
<3FC7A565.4000104@klaster.net>
<296.1070049123@sss.pgh.pa.us>
<3FC7AC6F.7080603@klaster.net>
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=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-Jürgen
Schönig.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jasiek@klaster.net
Subject: Re: explicit joins wrong planning
In-Reply-To: <1517.1070056638@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox