I've spoken to Merlin off-list and confirmed this issue will be fixed in the locale fix I'll post shortly. The reason being that the backend thought it was in a non-C locale (the window system default locale which is specifically *not* C), and in non-C locale LIKE is not indexable.
//Magnus >-----Original Message----- >From: Merlin Moncure [mailto:[EMAIL PROTECTED] >Sent: den 25 maj 2004 22:53 >To: [EMAIL PROTECTED] >Cc: [EMAIL PROTECTED] >Subject: [pgsql-hackers-win32] another optimizer bug? > > >Following example is with latest anonymous cvs of 7.5. > >I can't get LIKE to use an index even with seq_scan = off. I'm using >the default locale and hchassis.vin_no is defined as char(17). The >hchassis table has about 250k rows in it. The non aggregate versions of >the selects have the same results WRT the optimizer. Varying the VIN >makes no difference. > >Simple form: >select a from b where a like 'k%'; > >Am I crazy? This is a query I would normally expect to always use the >index. > >Merlin > >Log: [first two queries with like, second two with =] >cpc=# explain select count(vin_no) from hchassis where vin_no like >'2FTZX08W8WCA21580%'; > QUERY PLAN >------------------------------------------------------------------- > Aggregate (cost=19576.22..19576.22 rows=1 width=21) > -> Seq Scan on hchassis (cost=0.00..19576.21 rows=1 width=21) > Filter: (vin_no ~~ '2FTZX08W8WCA21580%'::text) >(3 rows) > >cpc=# select count(vin_no) from hchassis where vin_no like >'2FTZX08W8WCA21580%'; > count >------- > 1 >(1 row) > >cpc=# >cpc=# explain select count(vin_no) from hchassis where vin_no = >'2FTZX08W8WCA21580'; > QUERY PLAN >--------------------------------------------------------------- >--------- >------------------- > Aggregate (cost=5.61..5.61 rows=1 width=21) > -> Index Scan using hchassis_vin_no_idx on hchassis >(cost=0.00..5.60 rows=1 width=21) > Index Cond: (vin_no = '2FTZX08W8WCA21580'::bpchar) >(3 rows) > > count >------- > 1 >(1 row) > > > >---------------------------(end of >broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
