X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 546B0D1EC81 for ; Wed, 24 Mar 2004 12:11:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 50985-07 for ; Wed, 24 Mar 2004 08:11:29 -0400 (AST) Received: from aari.shi.ar (shiar.net [213.84.17.17]) by svr1.postgresql.org (Postfix) with ESMTP id B102FD1ECB9 for ; Wed, 24 Mar 2004 08:11:25 -0400 (AST) Received: by aari.shi.ar (Postfix, from userid 1000) id 030F131146; Wed, 24 Mar 2004 13:11:25 +0100 (CET) Date: Wed, 24 Mar 2004 13:11:25 +0100 From: Shiar To: pgsql-performance@postgresql.org Subject: bigint index not used Message-ID: <20040324121125.GE1178@shiar.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Originating-IP: [172.18.105.9] X-Mailer: Mutt 1.5.4i + Vim 6.2 (Debian Sid GNU/Linux 2.6.2) X-Face: "P0K@jx#[}DSMuY-PotP_k(0n(#0)rx.S*^vxoR}]K; %#76#:c}E* kt)ndb/Q,lI"H/+aZH X-URL: http://shiar.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/384 X-Sequence-Number: 6240 We've got a table containing userdata, such as a bigint column 'icq'. To easily check whether a user has an icq number entered, we made the following index: userinfo_icq_ne0_id_key btree (id) WHERE (icq <> 0::bigint), However, it doesn't seem to be used: > EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0'; Seq Scan on userinfo (cost=0.00..47355.90 rows=849244 width=4) (actual time=0.563..1222.963 rows=48797 loops=1) Filter: (icq <> 0::bigint) Total runtime: 1258.703 ms > SET enable_seqscan TO off; > EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0'; Index Scan using userinfo_icq_ne0_id_key on userinfo (cost=0.00..65341.34 rows=48801 width=4) (actual time=0.124..256.478 rows=48797 loops=1) Filter: (icq <> 0::bigint) Total runtime: 290.804 ms It would even rather use much larger indexes, for example the integer pics with index: userinfo_pics_gt0_id_key btree (id) WHERE (pics > 0), > EXPLAIN ANALYZE SELECT id FROM userinfo WHERE icq <> '0' AND pics > 0; Index Scan using userinfo_pics_gt0_id_key on userinfo (cost=0.00..60249.29 rows=323478 width=4) (actual time=0.039..1349.590 rows=23500 loops=1) Filter: ((icq <> 0::bigint) AND (pics > 0)) Total runtime: 1368.227 ms We're running PostgreSQL 7.4.1 on a Debian/Linux 2.4 system with 4GB RAM and a fast SCSI RAID array, with settings: shared_buffers = 65536 # min max_connections*2 or 16, 8KB each sort_mem = 16384 # min 64, size in KB effective_cache_size = 327680 # typically 8KB each random_page_cost = 1.5 # 4 # units are one sequential page fetch cost -- Shiar - http://www.shiar.org > Mi devas forfughi antau fluganta nubskrapulo alterighos sur mia kapo