From: Shiar <shiar@shiar.org>
To: pgsql-performance@postgresql.org
Subject: bigint index not used
Date: Wed, 24 Mar 2004 13:11:25 +0100
Message-ID: <20040324121125.GE1178@shiar.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: shiar@shiar.org
Subject: Re: bigint index not used
In-Reply-To: <20040324121125.GE1178@shiar.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox