X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9CC41475F3D for ; Tue, 8 Apr 2003 22:00:06 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 0B50B474E42 for ; Tue, 8 Apr 2003 21:59:34 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id ED552D60B; Tue, 8 Apr 2003 18:59:37 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id E30EE5C0B; Tue, 8 Apr 2003 18:59:37 -0700 (PDT) Date: Tue, 8 Apr 2003 18:59:37 -0700 (PDT) From: Stephan Szabo To: "Denis @ Next2Me" Cc: , Subject: Re: [SQL] Yet Another (Simple) Case of Index not used In-Reply-To: Message-ID: <20030408185412.C57783-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Spam-Status: No, hits=-25.8 required=5.0 tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/80 X-Sequence-Number: 1586 On Tue, 8 Apr 2003, Denis @ Next2Me wrote: > The kind of requests that I am really interested in are: > select count(*) from table where table.column like 'pattern%' If you think an index scan should be faster, you can try set enable_seqscan=off; and see if that changes the plan generated by explain and with analyze you can compare the time used. Without information on the estimated selectivity it's hard to say what's right. If it doesn't use the index (ie, it's still using a sequential scan) after the enable_seqscan=off it's likely that you didn't initdb in "C" locale in which case like won't use indexes currently (you can see the archives for long description, but the short one is that some of the locale rules can cause problems with using the index).