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 124E63A3D50 for ; Fri, 5 Nov 2004 17:56:51 +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 06804-08 for ; Fri, 5 Nov 2004 17:56:49 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 690BC3A3D27 for ; Fri, 5 Nov 2004 17:56:48 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 58so64856wri for ; Fri, 05 Nov 2004 09:56:47 -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=e60T/GclOvtHi7IRipWBaHn7EzFOAJDsqnpsP7+MQXktq8ELC1NxLcMW2OTJ82fFxqhl5PkxKSiUdbgIYH97aKJJg1YJu26DpoRsAPtTZDN9NPN3YFM8ZWOMC5VQTjwlwBBigT2Ti+OG3dkjZlfQK6eF1BTYqszyBA+rHaLnzEk= Received: by 10.54.21.14 with SMTP id 14mr89096wru; Fri, 05 Nov 2004 09:56:47 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 5 Nov 2004 09:56:47 -0800 (PST) Message-ID: <88f1825a041105095631064cb0@mail.gmail.com> Date: Fri, 5 Nov 2004 12:56:47 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? In-Reply-To: <20041105175159.GB8855@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <88f1825a041105063955367088@mail.gmail.com> <20041105175159.GB8855@wolff.to> 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/102 X-Sequence-Number: 9056 On Fri, 5 Nov 2004 11:51:59 -0600, Bruno Wolff III wrote: > On Fri, Nov 05, 2004 at 09:39:16 -0500, > Allen Landsidel wrote: > > > > For some reason it's a requirement that partial wildcard searches are > > done on this field, such as "SELECT ... WHERE field LIKE 'A%'" > > > > I thought an interesting way to do this would be to simply create > > partial indexes for each letter on that field, and it works when the > > query matches the WHERE clause in the index exactly like above. The > > problem is thus: > > That may not help much except for prefixes that have a below average > number of occurences. If you are going to be select 1/26 of the records, > you are probably going to do about as well with a sequential scan as an > index scan. The thing isn't that I want 1/26th of the records since the distribution is not exactly equal among different letters, but more importantly, there are about 76million rows currently, and for some reason I am being told by the people with the pointy hair that a query like "select foo,bar from table where foo like 'abc%';" is not an uncommon type of query to run. I don't know why it's common and to be honest, I'm afraid to ask. ;) With that many rows, and a normal index on the field, postgres figures the best option for say "I%" is not an index scan, but a sequential scan on the table, with a filter -- quite obviously this is slow as heck, and yes, I've run analyze several times and in fact have the vacuum analyze automated. With the partial index the index scan is used and the cost drops from 0..2million to 0..9000 -- a vast improvement. So I'm going to go with the partial indexes, and have a total of 36 of them -- A-Z and 0-9. > Just having a normal index on the column will work if the database locale > is C. In 7.4 you can create an index usable by LIKE even in the database > locale isn't C, but I don't remember the exact syntax. You will be better > off having just one index rather than 26 partial indexes. I haven't written a line of C in years, and it was never my strong suit, so despite all my years doing development and sysadminning, the locale stuff is still something of a mystery to me. The locale though is C, the default, and will for the time being at least be storing only ascii strings -- no unicode, other character sets, or anything funky like that. -Allen