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)