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 990F43A3D33 for ; Fri, 5 Nov 2004 19:57:58 +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 41904-04 for ; Fri, 5 Nov 2004 19:57:49 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7FCB63A3E6B for ; Fri, 5 Nov 2004 19:57:50 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iA5JveOe010899; Fri, 5 Nov 2004 14:57:41 -0500 (EST) To: Allen Landsidel Cc: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? In-reply-to: <88f1825a041105095631064cb0@mail.gmail.com> References: <88f1825a041105063955367088@mail.gmail.com> <20041105175159.GB8855@wolff.to> <88f1825a041105095631064cb0@mail.gmail.com> Comments: In-reply-to Allen Landsidel message dated "Fri, 05 Nov 2004 12:56:47 -0500" Date: Fri, 05 Nov 2004 14:57:40 -0500 Message-ID: <10898.1099684660@sss.pgh.pa.us> From: Tom Lane 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/108 X-Sequence-Number: 9062 Allen Landsidel writes: > 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. Hmm. This suggests to me that you're using a non-C locale and so a plain index *can't* be used for a LIKE query. Can you force it to use an indexscan by setting enable_seqscan = false? If not then you've got a locale problem. As someone else pointed out, this can be worked around by creating an index with the right operator class. regards, tom lane