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 2EC6A3A3E1D for ; Fri, 5 Nov 2004 20:36:34 +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 52599-01 for ; Fri, 5 Nov 2004 20:36:28 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.206]) by svr1.postgresql.org (Postfix) with ESMTP id D25D43A3C1E for ; Fri, 5 Nov 2004 20:36:28 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 58so70103wri for ; Fri, 05 Nov 2004 12:36:28 -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=DXWH6pIgaJVBgDXyEyTcoDj5zUVLWcmca4T9On7+uLP9P0KOZ1d4hUVZZxTHIlV+rVmd+mx5wxB/DQumEUXllAdbIUzuU5d2qE0KYmbjNk3B08pRezwCbssK6QZqaTATrcLgUb/RhcsR3g3zrBdpdG0R8onvIv5Qahr6+5KXGiE= Received: by 10.54.21.61 with SMTP id 61mr80683wru; Fri, 05 Nov 2004 12:36:28 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 5 Nov 2004 12:36:28 -0800 (PST) Message-ID: <88f1825a0411051236497f6bbf@mail.gmail.com> Date: Fri, 5 Nov 2004 15:36:28 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: pgsql-performance@postgresql.org Subject: Re: Strange (?) Index behavior? In-Reply-To: <10898.1099684660@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <88f1825a041105063955367088@mail.gmail.com> <20041105175159.GB8855@wolff.to> <88f1825a041105095631064cb0@mail.gmail.com> <10898.1099684660@sss.pgh.pa.us> 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/110 X-Sequence-Number: 9064 On Fri, 05 Nov 2004 14:57:40 -0500, Tom Lane wrote: > 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. Tom, disabling seqscan does cause it to use the index. With seqscan enabled however, "AB%" will use the index, but "A%" will not. The estimated cost for the query is much higher without the partial indexes than it is with them, and the actual runtime of the query is definitely longer without the partial indexes. The locale is set in the postgresql.conf file as per default, with.. # These settings are initialized by initdb -- they may be changed lc_messages = 'C' # locale for system error message strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting -Allen