bug in index?

Started by Martin, Sylvain R. (LNG)almost 26 years ago2 messagesbugs
Jump to latest
#1Martin, Sylvain R. (LNG)
Sylvain.Martin@lexis-nexis.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Martin, Sylvain R. (LNG) (#1)
Re: bug in index?

I had your prior message on my "to look at" list; the behavior seems
pretty odd. Two questions:

1. Which version are you running, exactly?

2. Might 'peripherals & access' happen to be the largest (last in
sort order) keyword present in your table?

If #2 is true, then this might represent some sort of boundary-condition
misbehavior in the cost estimator. But I don't see anything like it
happening in current sources.

regards, tom lane