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 3155F5E46C2 for ; Mon, 30 Aug 2004 14:46: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 80392-05 for ; Mon, 30 Aug 2004 17:46:39 +0000 (GMT) Received: from emepe3.com (unknown [69.93.218.52]) by svr1.postgresql.org (Postfix) with ESMTP id B73D25E46E7 for ; Mon, 30 Aug 2004 14:46:39 -0300 (ADT) Received: from [200.89.130.112] (helo=[192.168.0.101]) by emepe3.com with asmtp (Exim 3.35 #1 (Debian)) id 1C1qVQ-0007nk-00 for ; Mon, 30 Aug 2004 13:03:48 -0500 Subject: seqscan instead of index scan From: Martin Sarsale To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-CNz/VnUoCsQ0YLwj9l6S" Organization: Emepe3.com Inc Message-Id: <1093887997.1680.71.camel@kadaif> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 30 Aug 2004 14:46:37 -0300 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/445 X-Sequence-Number: 8081 --=-CNz/VnUoCsQ0YLwj9l6S Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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: 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 --=-CNz/VnUoCsQ0YLwj9l6S Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQBBM2f8hruVGj+ub3MRAqNlAJ0fhruL7t5DLlQW6kHf99jtgzPuuQCgqAN1 BoC6ca++eVHj89y+X+sEZho= =Qvza -----END PGP SIGNATURE----- --=-CNz/VnUoCsQ0YLwj9l6S--