>> SELECT toode, nimetus >> FROM toode >> WHERE toode ILIKE 'x10%' ESCAPE '!' >> ORDER BY UPPER(toode ),nimetus LIMIT 100 >> >> runs 1 minute in first time for small table size. >> >> Toode field type is CHAR(20) > > 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you > problems using an index, period. 1. I haven't seen any example where VARCHAR is better that CHAR for indexing 2. I have a lot of existing code. Changing CHAR to VARCHAR requires probably re-writing a lot of code, a huge work. > 2) You can't use an index on ILIKE. I'ts very sad. I expected that lower(toode) index can be used. > You can, however, use an index on > lower(field) if your query is properly phrased and if you've created an > expression index on lower(field). I tried by Postgres does not use index. Why ? 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"
