Speeding up startswith query - Mailing list pgsql-general

I ran analyze and tried command

explain analyze
SELECT * FROM dok WHERE doktyyp=E'O'       AND ('0'::float8 =0 or
dok.tasumata<>0) AND
        ('0'::float8 =0 or NOT dok.taidetud) AND dok.sihtyksus LIKE
'RIISIPERE%' ESCAPE '!' AND kuupaev BETWEEN '2008-05-01' AND '2999-08-31'
ORDER BY dokumnr  LIMIT 7676868

"Limit  (cost=125496.69..125497.67 rows=392 width=1173) (actual
time=103151.904..103160.615 rows=792 loops=1)"
"  ->  Sort  (cost=125496.69..125497.67 rows=392 width=1173) (actual
time=103151.894..103154.811 rows=792 loops=1)"
"        Sort Key: dokumnr"
"        ->  Index Scan using dok_kuupaev_idx on dok  (cost=0.00..125479.81
rows=392 width=1173) (actual time=2.210..103144.406 rows=792 loops=1)"
"              Index Cond: ((kuupaev >= '2008-05-01'::date) AND (kuupaev <=
'2999-08-31'::date))"
"              Filter: ((doktyyp = 'O'::bpchar) AND (sihtyksus ~~
'RIISIPERE%'::text))"
"Total runtime: 103163.924 ms"

It returns only 792 rows.
Without doktyyp=E'O'  conditon it returns  68389  rows.
If LIKE constraint is also removed it returns 308672 rows.

dok.sihtyksus type is CHAR(10) NULL
db encoding is utf-8 and cluster has custom locale.
This query can use invariant (english) locale for comparison, it does not
use anything which require locale specific comparison.

Is it best way to create

CREATE UNIQUE INDEX dok_sihtyksus_unique_pattern_idx ON dok(sihtyksus
text_pattern_ops);

to speed it up ?

Can upgrade latest stable version speed it up ?

Andrus.

Using

"PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0,
pie-8.7.9)"