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. -----Original Message----- From: Martin, Sylvain R. (LNG) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 05, 2000 10:56 AM To: '[EMAIL PROTECTED]' Subject: [SQL] bug in using index scans? 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) Sylvain Martin USA-Response Team (937) 865-6800 x4432 Pager: (937) 636-1171
