I know this is an FAQ, but each case seems to be different. I can not
understand why the primary index on this table is not used. It was
specifically created to make this query run quickly.
admin=# \d client_usage
Table "client_usage"
Column | Type | Modifiers
----------+----------------------+-----------
client | smallint |
userid | character varying(8) |
period | character(6) |
resource | smallint |
tstamp | date |
zone | smallint |
cnt | integer |
vol | integer |
Indexes: client_usage_indx1
admin=# \d client_usage_indx1
Index "client_usage_indx1"
Column | Type
--------+----------------------
client | smallint
userid | character varying(8)
tstamp | date
btree
admin=# vacuum verbose analyze client_usage;
NOTICE: --Relation client_usage--
NOTICE: Pages 23101: Changed 0, Empty 0; Tup 2365648: Vac 0, Keep 0,
UnUsed 0.
Total CPU 0.57s/0.08u sec elapsed 30.01 sec.
NOTICE: Analyzing client_usage
VACUUM
admin=# explain SELECT zone, tstamp, sum( vol )
admin-# FROM client_usage
admin-# WHERE userid='events' and client=10143 and
admin-# tstamp >= '1-1-2002' and tstamp < '1-1-2003'
admin-# group by zone, tstamp;
NOTICE: QUERY PLAN:
Aggregate (cost=70418.33..70419.27 rows=13 width=10)
-> Group (cost=70418.33..70418.96 rows=125 width=10)
-> Sort (cost=70418.33..70418.33 rows=125 width=10)
-> Seq Scan on client_usage (cost=0.00..70413.96
rows=125 width=10)
EXPLAIN
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
