Re: another optimizer bug?

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:merlin(dot)moncure(at)rcsonline(dot)com]
>Sent: den 25 maj 2004 22:53
>To: pgsql-hackers(at)postgresql(dot)org
>Cc: pgsql-hackers-win32(at)postgresql(dot)org
>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
>