pgsql-admin-owner@postgresql.org wrote on 31/03/2002 16:28:09:
>
> First question is *can* the thing use an index? (Try "set
enable_seqscan
> to off" then explain again.) If not, it's probably a datatype
> compatibility issue --- you'll need to quote or explicitly cast the
> constant 1017589362 to match the type of suspend_expires.
>
Yep, it does use the index in that case:
---
cmaster=# set enable_seqscan to off;
SET VARIABLE
cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
suspend_expires <= 1017589362 AND suspend_expires <> 0;
NOTICE: QUERY PLAN:
Index Scan using levels_suspendexpires_idx on levels (cost=0.00..37098.40
rows=2787 width=8) (actual time=2551.05..2551.05 rows=0 loops=1)
Total runtime: 2551.17 msec
---
However its not much faster, so I took a look at the data distribution in
that table and quite a large amount of the data is 0 most of the time.
After a quick dig around some new 7.2 features I stumbled upon partial
index support:
---
cmaster=# \d levels_suspendexpires_idx
Index "levels_suspendexpires_idx"
Column | Type
-----------------+---------
suspend_expires | integer
btree
Index predicate: (suspend_expires <> 0)
---
cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
suspend_expires <= 1017605805 AND suspend_expires <> 0;
NOTICE: QUERY PLAN:
Index Scan using levels_suspendexpires_idx on levels (cost=0.00..267.65
rows=1621 width=8) (actual time=0.06..0.11 rows=6 loops=1)
Total runtime: 0.19 msec
---
Much better ;)
Thanks for your time and advice :)
Cheers, Greg.