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 237A35E46EE for ; Mon, 30 Aug 2004 14:53:44 -0300 (ADT) 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 79856-08 for ; Mon, 30 Aug 2004 17:53:39 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 4546A5E46E1 for ; Mon, 30 Aug 2004 14:53:38 -0300 (ADT) Received: (qmail 32550 invoked by uid 500); 30 Aug 2004 18:02:27 -0000 Date: Mon, 30 Aug 2004 13:02:27 -0500 From: Bruno Wolff III To: Martin Sarsale Cc: pgsql-performance@postgresql.org Subject: Re: seqscan instead of index scan Message-ID: <20040830180227.GA32520@wolff.to> Mail-Followup-To: Martin Sarsale , pgsql-performance@postgresql.org References: <1093887997.1680.71.camel@kadaif> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1093887997.1680.71.camel@kadaif> 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: 200408/446 X-Sequence-Number: 8082 On Mon, Aug 30, 2004 at 14:46:37 -0300, Martin Sarsale wrote: > Dear all: > > Im having a weird problem here. I have a table w/ ~180.000 rows and I > want to select those where c > 0 or d > 0 (there only a few of those on > the table) > I indexed columns c and d (separately) but this query used the slow > seqscan instead of the index scan: Postgres doesn't 'or' bitmaps derived from two indexes. You might have more luck using a combined index. > > select * from t where c<>0 or d<>0; > > After playing some time, I noticed that if I change the "or" for an > "and", pg used the fast index scan (but the query w/ 'and' was not what > I as looking for). > > Then, I thought I could do the following: > Creating an index with the expression (c+d) and selecting the rows where > c+d > 0: > select * from t where c + d <> 0; > > Again, this used a seqscan. Asking in #postgresql in freenode, somebody > told me to try to disable seqscan (set enable_seqscan false) and > suprisingly, Pg started using the index scan and it was -fast-. > > Now: I've no idea why it chooses to use a seq scan instead of the index > scan (yes, I've just vacuum analyzed the table before running the > query). > > Some more info: > c and d are both bigint. I've tried the queries casting the constant (0) > to bigint but nothing changed. > > Im using debian's pg 7.4.1-2. > > > Thanks in advance >