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

bug in index?

From: "Martin, Sylvain R(dot) (LNG)" <Sylvain(dot)Martin(at)lexis-nexis(dot)com>
To: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: bug in index?
Date: 2000-07-07 18:56:07
Message-ID: 7985A5B6EB66D311B6350008C791487A029BFB98@lnxdayexch08.lexis-nexis.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I've notice on certain queries, I was waiting a long time for a return so I
set out to troubleshoot something here's what I ran into...
When I do a explain on a select looking for 'peripherals & access' it uses
sequential scan but any other keyword uses index scan.
I've ran the vacuum analyze on PI_Keywords but comes up with the same
results. Can anyone offer some insight or confirm this as a bug?

rvbs=# \d PI_Keywords
       Table "pi_keywords"
 Attribute |   Type   | Modifier 
-----------+----------+----------
 keyword   | char(50) | 
 productid | integer  | 
Index: pi_keywords_idx

rvbs=# select distinct (keyword) from PI_Keywords where keyword like '%&%'
limit 10;
                       keyword                       
----------------------------------------------------
 adult training & ed                               
 atlas & mapping                                   
 books & manuals                                   
 chips & modules                                   
 education & training                              
 peripherals & access                              
(6 rows)

rvbs=# explain select * from PI_Keywords where keyword like 'peripherals &
access'; 
NOTICE:  QUERY PLAN:

Seq Scan on pi_keywords  (cost=0.00..6596.18 rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'chips &
modules'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'education &
training'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'adult training
& ed'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'peripherals
&%'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..4.40 rows=12632
width=16)

EXPLAIN

rvbs=# select distinct(keyword) from PI_Keywords where keyword like
'peripherals &%';
                      keyword                       
----------------------------------------------------
 peripherals & access                              
(1 row)

just for the heck of it I decided to run the following in case it helped

rvbs=# explain select count(*) from PI_Keywords where keyword like
'peripherals & access%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=35.98..35.98 rows=1 width=4)
  ->  Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..4.40
rows=12632 width=4)

EXPLAIN
rvbs=# explain select count(*) from PI_Keywords where keyword like
'peripherals & access';
NOTICE:  QUERY PLAN:

Aggregate  (cost=6599.33..6599.33 rows=1 width=4)
  ->  Seq Scan on pi_keywords  (cost=0.00..6596.18 rows=1263 width=4)

EXPLAIN
Apparently adding a % at the end made it use the index scan.


Sylvain Martin
USA-Response Team
(937) 865-6800 x4432
Pager: (937) 636-1171


Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2000-07-07 19:13:05
Subject: Re: ps_status.h on FreeBSD 4.0 problems and fix
Previous:From: Tom LaneDate: 2000-07-07 18:12:34
Subject: Re: Re: [HACKERS] Re: [SQL] MAX() of 0 records.

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