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 4FE463A3E14 for ; Fri, 5 Nov 2004 17:40: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 99998-07 for ; Fri, 5 Nov 2004 17:40:48 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 1326C3A3E03 for ; Fri, 5 Nov 2004 17:40:47 +0000 (GMT) Received: (qmail 15874 invoked by uid 500); 5 Nov 2004 17:51:59 -0000 Date: Fri, 5 Nov 2004 11:51:59 -0600 From: Bruno Wolff III To: Allen Landsidel Cc: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? Message-ID: <20041105175159.GB8855@wolff.to> Mail-Followup-To: Allen Landsidel , pgsql-performance@postgresql.org References: <88f1825a041105063955367088@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <88f1825a041105063955367088@mail.gmail.com> User-Agent: Mutt/1.5.6i 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/100 X-Sequence-Number: 9054 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. 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.