X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C94C9475A80 for ; Sat, 29 Mar 2003 12:54:56 -0500 (EST) Received: from ms-smtp-02.texas.rr.com (ms-smtp-02.texas.rr.com [24.93.36.230]) by postgresql.org (Postfix) with ESMTP id 8E5B2475A8D for ; Sat, 29 Mar 2003 12:54:55 -0500 (EST) Received: from spaceship.com (cs24243214-140.austin.rr.com [24.243.214.140]) by ms-smtp-02.texas.rr.com (8.12.5/8.12.2) with ESMTP id h2THt0df001360 for ; Sat, 29 Mar 2003 12:55:00 -0500 (EST) Message-ID: <3E85DDF4.7060406@spaceship.com> Date: Sat, 29 Mar 2003 11:55:00 -0600 From: Matt Mello User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3b) Gecko/20030210 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Index not used, performance problem References: <20030329214139.N4697-100000@houston.familyhealth.com.au> <1048957151.7543.19.camel@andi-lap> In-Reply-To: <1048957151.7543.19.camel@andi-lap> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-38.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/195 X-Sequence-Number: 1493 I have many boolean columns, and my queries almost always use indexes. Just because a column can have only 2 values does not mean that 50% of them will be true and 50% will be false. The ratio of T|F depends on the content. I have some boolean columns with less than 1% true. Obviously, an index will help with these ... and it does, tremendously. If you only have 6400 rows, it is *possible* that the planner will choose not to use an index, as using an index might be slower than just seqscanning. If you do lots of updates on that table, you might need to do a vacuum full occasionally, although I'm not certain how much that benefits a boolean field. Also, if possible, I would consider upgrading to a more recent version. I have seen many of the experts here post news about significant bug fixes between 7.2 and 7.3. (My experience with boolean fields is using 7.3.) In addition, when posting to the list, it is helpful to post an "explain analyze" for a query, as it gives more & better details (for those same experts, of which I am not). Andreas Kostyrka wrote: > On Sat, 2003-03-29 at 14:47, Christopher Kings-Lynne wrote: > >>Hi Andreas, >> >>A few points: >> >>PostgreSQL is rarely going to use an index for a boolean column. The >>reason is that since almost by definition true will occupy 50% of the rows >>and false will occupy 50% (say). In this case, a sequential scan is >>always faster. You would say that the 'selectivity' isn't good enough. > > Well, perhaps it should collect statistics, because a "deleted" column > is a prime candidate for a strongly skewed population. > > Andreas -- Matt Mello 512-350-6900