X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 455339DC9EB for ; Wed, 11 Jan 2006 11:33:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99763-08 for ; Wed, 11 Jan 2006 11:33:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 05FE89DC9F0 for ; Wed, 11 Jan 2006 11:33:05 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0BFX3Y6012878; Wed, 11 Jan 2006 10:33:03 -0500 (EST) To: Robert Creager cc: Michael Fuhr , PGPerformance Subject: Re: Index isn't used during a join. In-reply-to: <20060111072659.7c1772ad@thunder.logicalchaos.org> References: <20060109212338.6420af12@thunder.logicalchaos.org> <20060110055818.GA55076@winnie.fuhr.org> <20060110221055.053596ab@thunder.logicalchaos.org> <20060111075655.GA51292@winnie.fuhr.org> <20060111072659.7c1772ad@thunder.logicalchaos.org> Comments: In-reply-to Robert Creager message dated "Wed, 11 Jan 2006 07:26:59 -0700" Date: Wed, 11 Jan 2006 10:33:03 -0500 Message-ID: <12877.1136993583@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.083 required=5 tests=[AWL=0.083] X-Spam-Score: 0.083 X-Spam-Level: X-Archive-Number: 200601/113 X-Sequence-Number: 16591 Robert Creager writes: > What I had thought is that PG would (could?) be smart enough to realize tha= > t one query was restricted, and apply that restriction to the other based o= > n the join. I know it works in other cases (using indexes on both tables u= > sing the join)... The planner understands about transitivity of equality, ie given a = b and b = c it can infer a = c. It doesn't do any such thing for inequalities though, nor does it deduce f(a) = f(b) for arbitrary functions f. The addition Michael suggested requires much more understanding of the properties of the functions in your query than I think would be reasonable to put into the planner. regards, tom lane