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 F05A3539DB for ; Thu, 7 Apr 2005 15:14:31 +0100 (BST) 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 85438-06 for ; Thu, 7 Apr 2005 14:14:22 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 93015539C8 for ; Thu, 7 Apr 2005 15:14:20 +0100 (BST) Received: (qmail 29885 invoked by uid 500); 7 Apr 2005 14:31:20 -0000 Date: Thu, 7 Apr 2005 09:31:20 -0500 From: Bruno Wolff III To: Tom Lane Cc: Arjen van der Meijden , pgsql-hackers@postgreSQL.org, pgsql-performance@postgreSQL.org Subject: Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient) Message-ID: <20050407143120.GA29575@wolff.to> Mail-Followup-To: Bruno Wolff III , Tom Lane , Arjen van der Meijden , pgsql-hackers@postgreSQL.org, pgsql-performance@postgreSQL.org References: <425413D3.5030304@vulcanus.its.tudelft.nl> <28552.1112809334@sss.pgh.pa.us> <425423A9.9000504@vulcanus.its.tudelft.nl> <5999.1112820690@sss.pgh.pa.us> <6326.1112825377@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6326.1112825377@sss.pgh.pa.us> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/152 X-Sequence-Number: 11549 On Wed, Apr 06, 2005 at 18:09:37 -0400, Tom Lane wrote: > > Can anyone suggest a more general rule? Do we need for example to > consider whether the relation membership is the same in two clauses > that might be opposite sides of a range restriction? It seems like > > a.x > b.y AND a.x < b.z > > probably can be treated as a range restriction on a.x for this purpose, > but I'm much less sure that the same is true of > > a.x > b.y AND a.x < c.z > > Thoughts? I think it makes sense to guess that a smaller fraction of the rows will be returned when a column value is bounded above and below than if it is only bounded on one side, even if the bounds aren't fixed. You can certainly be wrong. The difference between this and the normal case is that column statistics aren't normally going to be that useful. If date/time ranges are the common use for this construct, it might be better to create date and/or time range types that use rtree or gist indexes.