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

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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)



pgsql-hackers by date

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

pgsql-hackers-win32 by date

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

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