How to force postgres to use index for the following query (I can change the query to equivalent if required)
select nimi from klient where lower(nimi) like 'test%' Currently it does NOT use index: create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops); explain analyze select nimi from firma1.klient where lower(nimi) like 'mokter%' "Seq Scan on klient (cost=0.00..9.79 rows=1 width=74) (actual time=0.740..0.761 rows=1 loops=1)" " Filter: (lower((nimi)::text) ~~ 'mokter%'::text)" "Total runtime: 0.877 ms" Postgres 8.1 UTF8 encoding in Windows XP Note. Query explain analyze select nimi from firma1.klient where nimi like 'Mokter%' for same data uses index: "Index Scan using nimib on klient (cost=0.00..5.80 rows=1 width=74) (actual time=9.402..9.427 rows=1 loops=1)" " Index Cond: ((nimi ~>=~ 'Mokter'::bpchar) AND (nimi ~<~ 'Moktes'::bpchar))" " Filter: (nimi ~~ 'Mokter%'::text)" "Total runtime: 9.615 ms" Andrus. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
