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)
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
Show quoted text
-----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?
Import Notes
Resolved by subject fallback