X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 616E5D1B446 for ; Fri, 28 Nov 2003 21:57:56 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05837-10 for ; Fri, 28 Nov 2003 17:57:30 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2A695D1B433 for ; Fri, 28 Nov 2003 17:57:28 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hASLvI19001518; Fri, 28 Nov 2003 16:57:18 -0500 (EST) To: Tomasz Myrta Cc: Lista dyskusyjna pgsql-sql Subject: Re: explicit joins wrong planning 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> Comments: In-reply-to Tomasz Myrta message dated "Fri, 28 Nov 2003 21:13:35 +0100" Date: Fri, 28 Nov 2003 16:57:18 -0500 Message-ID: <1517.1070056638@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/333 X-Sequence-Number: 16148 Tomasz Myrta 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