Skip site navigation (1) Skip section navigation (2)

voodoo index usage ;)

From: "D(dot) Duccini" <duccini(at)backpack(dot)com>
To: Pgsql-novice <pgsql-novice(at)postgreSQL(dot)org>
Subject: voodoo index usage ;)
Date: 2001-03-17 19:36:55
Message-ID: Pine.GSO.4.03.10103171332200.20307-100000@ra.bpsi.net (view raw or flat)
Thread:
Lists: pgsql-novice
perhaps the db gawds can explain this....

# \d radusage
          Table "radusage"
 Attribute |    Type     | Modifier 
-----------+-------------+----------
 datetime  | timestamp   | not null
 account   | varchar(64) | 
 usage     | integer     | 
 sent      | integer     | 
 recv      | integer     | 
 ip        | bigint      | 
 host      | bigint      | 
 port      | smallint    | 
Indices: idxradaccount,
         idxraddate,
         idxradoid

# \d idxradaccount
  Index "idxradaccount"
 Attribute |    Type     
-----------+-------------
 account   | varchar(64)
btree

# \d idxraddate
  Index "idxraddate"
 Attribute |   Type    
-----------+-----------
 datetime  | timestamp
btree

# explain select * from radusage where account = 'someuser';
NOTICE:  QUERY PLAN:

Seq Scan on radusage  (cost=0.00..13870.80 rows=5674 width=50)

and if i add in datetime (without effectively changing the semantic
meaning of the search)

# explain select * from radusage where account = 'someuser' and datetime >
'1900-01-01';
NOTICE:  QUERY PLAN:

Index Scan using idxradaccount on radusage  (cost=0.00..15295.37 rows=5668
width=50)


first case doesn't use the index, the second does use what would seem to
be the correct index

isn't that wacky????

-duck

-----------------------------------------------------------------------------
david(at)backpack(dot)com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-03-17 20:15:36
Subject: Re: voodoo index usage ;)
Previous:From: Randy HallDate: 2001-03-16 20:14:58
Subject: Re: pg_dump & BLOBs ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group