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 45CAE3A3CF0 for ; Fri, 5 Nov 2004 16:54:13 +0000 (GMT) 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 83257-04 for ; Fri, 5 Nov 2004 16:54:04 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.204]) by svr1.postgresql.org (Postfix) with ESMTP id 4C4593A3ACB for ; Fri, 5 Nov 2004 16:54:03 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 58so62895wri for ; Fri, 05 Nov 2004 08:54:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=SfORcWKfMxUphITcAQWsWS5Xmyi8+gUSxCESfPoHhtQXCI8tRdPm1VMsPYWtWBm5AmgqDuedCGsnLHbgYAJHdFm9rJtL6Um9Z10J41QSfkFHNSjHrSSRzmLf+umlCdSJOKmcsdHWhQ3cGIvNxmypASNWaG9+1qbQxOEwiDUrVJM= Received: by 10.54.21.14 with SMTP id 14mr83788wru; Fri, 05 Nov 2004 08:54:02 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 5 Nov 2004 08:54:02 -0800 (PST) Message-ID: <88f1825a0411050854417e1198@mail.gmail.com> Date: Fri, 5 Nov 2004 11:54:02 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: Postgresql Performance Subject: Re: Strange (?) Index behavior? In-Reply-To: <88f1825a041105073245010974@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <88f1825a041105063955367088@mail.gmail.com> <1099667258.1647.827.camel@home> <88f1825a041105073245010974@mail.gmail.com> 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: 200411/99 X-Sequence-Number: 9053 On Fri, 5 Nov 2004 10:32:43 -0500, Allen Landsidel wrote: > On Fri, 05 Nov 2004 10:07:38 -0500, Rod Taylor wrote: > > > > > It seems to me that a query saying "SELECT column FROM table WHERE > > > column LIKE 'AA%';" should be just as fast or very close to the first > > > case up above. However, explain tells me that this query is not using > > > the index above, which is what's not making sense to me. > > > > It looks for an exact expression match, and doesn't know about values > > which are equal. > > > > You can provide both clauses. > > > > WHERE column LIKE 'A%' and column LIKE 'AA%'; > > I see. That's not really optimal either however as you can probably > see already.. adding AB, AC, AD...AZ is likely to be pretty bogus and > at the least is time consuming. I see now that you mean to add that to the SELECT clause and not the index, my mistake. > Perhaps.. SELECT column FROM (SELECT column FROM table WHERE column > LIKE 'A%') AS sq WHERE column LIKE 'AA%'; > > The query planner thinks this will be pretty fast indeed, and does use > the index I am after. This was indeed pretty fast. About 7 seconds, as was modifying the WHERE as suggested above. -Allen