Thread: bigint index not used

bigint index not used

From
Shiar
Date:
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.478rows=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.590rows=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

Re: bigint index not used

From
Tom Lane
Date:
Shiar <shiar@shiar.org> writes:
>> 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

The rows estimate is way off, which might or might not have much to do
with the issue, but it's surely suspicious.

> 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:

Update to 7.4.2 and follow the procedure in the release notes about
fixing pg_statistic; that may make things better.  int8 columns are
vulnerable to the statistic misalignment bug.

            regards, tom lane