Thread

1. bug in index?

   Martin, Sylvain R. (LNG) <sylvain.martin@lexis-nexis.com> — 2000-07-07T18:56:07Z

   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
   
   
   

2. Re: bug in index?

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-07T19:37:14Z

   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