another optimizer bug?

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Cc: <pgsql-hackers-win32(at)postgresql(dot)org>
Subject: another optimizer bug?
Date: 2004-05-25 20:53:11
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AE38@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-hackers-win32

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)

Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2004-05-25 21:24:43 Re: another optimizer bug?
Previous Message Bruce Momjian 2004-05-25 20:48:25 Re: New horology failure

Browse pgsql-hackers-win32 by date

  From Date Subject
Next Message Magnus Hagander 2004-05-25 21:24:43 Re: another optimizer bug?
Previous Message Tom Lane 2004-05-25 14:52:50 Re: table alias