another optimizer bug?
От | Merlin Moncure |
---|---|
Тема | another optimizer bug? |
Дата | |
Msg-id | 6EE64EF3AB31D5448D0007DD34EEB34101AE38@Herge.rcsinc.local обсуждение исходный текст |
Список | pgsql-hackers |
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 по дате отправления: