Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B0900B5FE8B for ; Mon, 6 Jun 2011 06:47:23 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 73456-06 for ; Mon, 6 Jun 2011 09:47:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og120.obsmtp.com (exprod7og120.obsmtp.com [64.18.2.18]) by mail.postgresql.org (Postfix) with ESMTP id 1A716B5FE91 for ; Mon, 6 Jun 2011 06:47:15 -0300 (ADT) Received: from mail-ey0-f180.google.com ([209.85.215.180]) (using TLSv1) by exprod7ob120.postini.com ([64.18.6.12]) with SMTP ID DSNKTeyiI8uiFJlEbQvsAV6ghehG6mgtiqhK@postini.com; Mon, 06 Jun 2011 02:47:16 PDT Received: by eyg24 with SMTP id 24so1745141eyg.11 for ; Mon, 06 Jun 2011 02:47:14 -0700 (PDT) Received: by 10.14.11.28 with SMTP id 28mr1824601eew.86.1307353634327; Mon, 06 Jun 2011 02:47:14 -0700 (PDT) Received: from [192.168.1.183] (dsl-hkibrasgw2-ff7ac300-240.dhcp.inet.fi [88.195.122.240]) by mx.google.com with ESMTPS id g48sm3058884eea.12.2011.06.06.02.47.12 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 06 Jun 2011 02:47:13 -0700 (PDT) Message-ID: <4DECA21E.2090307@enterprisedb.com> Date: Mon, 06 Jun 2011 12:47:10 +0300 From: Heikki Linnakangas User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI; rv:1.9.2.17) Gecko/20110510 Icedove/3.1.10 MIME-Version: 1.0 To: Chetan Suttraway CC: Josh Berkus , Sam Wong , pgsql-performance@postgresql.org Subject: Re: Index use difference betweer LIKE, LIKE ANY? References: <01e801cbd4f0$5153d840$f3fb88c0$@hellosam.net> <4D696CFE.2010107@agliodbs.com> <4DECA15B.2000809@enterprisedb.com> In-Reply-To: <4DECA15B.2000809@enterprisedb.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201106/32 X-Sequence-Number: 43965 On 06.06.2011 12:43, Heikki Linnakangas wrote: > Also, even when safe, it's not clear that the transformation is always a > win. The left-hand expression could be expensive, in which case having > to evaluate it multiple times could hurt performance. Maybe yo Sorry, hit "send" too early. Maybe you could put in some heuristic to only do the transformation when the left-hand expression is cheap, or maybe use something like the CaseTestExpr to avoid multiple evaluation and still use the OR form. Also, if the array is very large, opening it into the OR form could increase plan time substantially, so we'd probably only want to do it if there's any Vars involved, and thus any chance of matching an index. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com