Wht the SEQ Scan ?
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
On 15 Sep 2002, Glen Eustace wrote:
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.
The one in this case is probably the smallint/bigint problem with
constants being converted to int4 when unquoted.
Try client='10143' or client=CAST(10143 as smallint) in the where
clauses instead of client=10143 and see if that gives you usage of the
index.
On Sun, 2002-09-15 at 12:20, Stephan Szabo wrote:
The one in this case is probably the smallint/bigint problem with
constants being converted to int4 when unquoted.
Try client='10143' or client=CAST(10143 as smallint) in the where
clauses instead of client=10143 and see if that gives you usage of the
index.
Brilliant, got it in one.
Thanks.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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