Thread: Re: [pgsql-hackers-win32] another optimizer bug?

Re: [pgsql-hackers-win32] another optimizer bug?

From
"Magnus Hagander"
Date:
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.moncure@rcsonline.com]
>Sent: den 25 maj 2004 22:53
>To: pgsql-hackers@postgresql.org
>Cc: pgsql-hackers-win32@postgresql.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
>