Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B5F999FB33C for ; Thu, 8 Feb 2007 22:14:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 33477-03 for ; Thu, 8 Feb 2007 22:14:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 5647D9FB33B for ; Thu, 8 Feb 2007 22:14:22 -0400 (AST) Received: from perhan.alvh.no-ip.org (200-126-100-50.bk7-dsl.surnet.cl [200.126.100.50]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l192EJN4030178; Thu, 8 Feb 2007 18:14:24 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id A55167DCA; Thu, 8 Feb 2007 23:14:14 -0300 (CLST) Date: Thu, 8 Feb 2007 23:14:14 -0300 From: Alvaro Herrera To: George Pavlov Cc: Tom Lane , pgsql-sql@postgresql.org Subject: Re: huge disparities in =/IN/BETWEEN performance Message-ID: <20070209021414.GU24069@alvh.no-ip.org> Mail-Followup-To: George Pavlov , Tom Lane , pgsql-sql@postgresql.org References: <8C5B026B51B6854CBE88121DBF097A86815F42@ehost010-33.exch010.intermedia.net> <7377.1170986068@sss.pgh.pa.us> <8C5B026B51B6854CBE88121DBF097A86815F81@ehost010-33.exch010.intermedia.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8C5B026B51B6854CBE88121DBF097A86815F81@ehost010-33.exch010.intermedia.net> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Thu, 08 Feb 2007 18:14:25 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.04 tagged_above=0 required=5 tests=AWL, BAYES_05, FORGED_RCVD_HELO, RCVD_IN_NJABL_PROXY, RCVD_IN_SORBS_SOCKS X-Spam-Level: X-Archive-Number: 200702/111 X-Sequence-Number: 27445 George Pavlov wrote: > the basic question i have is fairly clear though: why saying "where x = > 10" should be different (in ANY cicumstance, not just mine) from saying > "where x between 10 and 10" or from "where x in (select ... /* some > query that returns 10 */)" ??? I think the principle here is that the system is not gonna waste cycles on dumb queries. Supposedly, morphing "foo BETWEEN 10 and 10" into "foo=10" is not a trivial transformation, and it'd impose a planning cost on all non-dumb BETWEEN queries. That cost is best avoided: if you optimize for dumb users, the smart users then want you buried because you've lost performance doing useless work. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support