X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E7E67D1B21B for ; Tue, 15 Jun 2004 15:04:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15350-02 for ; Tue, 15 Jun 2004 18:04:43 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8D3A4D1B19F for ; Tue, 15 Jun 2004 15:04:41 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5FI4fuq003140; Tue, 15 Jun 2004 14:04:41 -0400 (EDT) To: Frank van Vugt Cc: pgsql-performance@postgresql.org Subject: Re: why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ?? In-reply-to: <200406151730.40464.ftm.van.vugt@foxi.nl> References: <200406150849.29470.ftm.van.vugt@foxi.nl> <296.1087305799@sss.pgh.pa.us> <200406151730.40464.ftm.van.vugt@foxi.nl> Comments: In-reply-to Frank van Vugt message dated "Tue, 15 Jun 2004 17:30:40 +0200" Date: Tue, 15 Jun 2004 14:04:41 -0400 Message-ID: <3139.1087322681@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/158 X-Sequence-Number: 7216 Frank van Vugt writes: > When the 'article.id < 50' constraint is added, it follows that > 'foo.article_id < 50' is a constraint as well. Why is this constraint not > used to avoid the seqscan on package? We don't attempt to make every possible inference (and I don't think you'd like it if we did). The current code will draw inferences about transitive equality, for instance given a = b and b = c it will infer a = c, if all three operators involved are mergejoinable. But given a = b and some arbitrary other constraint on b, it won't consider substituting a into that other constraint. This example doesn't persuade me that it would be worth expending the cycles to do so. Aside from the sheer cost of planning time, there are semantic pitfalls to consider. In some datatypes there are values that are "equal" according to the = operator but are distinguishable by other operators --- for example, zero and minus zero in IEEE-standard float arithmetic. We'd need a great deal of caution in determining what inferences can be drawn. regards, tom lane