Skip site navigation (1) Skip section navigation (2)

Re: another optimizer bug?

From: "Magnus Hagander" <mha(at)sollentuna(dot)net>
To: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>,<pgsql-hackers(at)postgresql(dot)org>
Cc: <pgsql-hackers-win32(at)postgresql(dot)org>
Subject: Re: another optimizer bug?
Date: 2004-05-25 21:24:43
Message-ID: 6BCB9D8A16AC4241919521715F4D8BCE34BB03@algol.sollentuna.se (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-hackers-win32
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
>

pgsql-hackers by date

Next:From: Gaetano MendolaDate: 2004-05-25 21:54:55
Subject: Re: Optimizer bug??
Previous:From: Merlin MoncureDate: 2004-05-25 20:53:11
Subject: another optimizer bug?

pgsql-hackers-win32 by date

Next:From: Douglas WilsonDate: 2004-05-26 06:08:00
Subject: Build problem
Previous:From: Merlin MoncureDate: 2004-05-25 20:53:11
Subject: another optimizer bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group