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