Re: Increasing pattern index query speed

Richard and Mario,

> You can't use xxx_pattern_ops indexes for non-pattern tests.

I missed regular index. Sorry for that. Now issue with testcase is solved.
Thank you very much.

I researched issue in live 8.1.4 db a bit more.
Performed vacuum and whole db reindex.
Tried several times to run two same pattern queries in quiet db.

additonal condition

AND dok.kuupaev BETWEEN date'2008-11-21' AND date'2008-11-21'

takes 239 seconds to run.

additonal condition

AND dok.kuupaev = date'2008-11-21'

takes 1 seconds.

Both query conditions are logically the same.
How to make BETWEEN query fast (real queries are running as between queries
over some date range)?

P.S. VACUUM issues warning that free space map 150000 is not sufficient,
160000 nodes reqired.
Two days ago after vacuum full there were 60000 used enties in FSM. No idea
why this occurs.

Andrus.

set search_path to firma2,public;
explain analyze SELECT sum(1)
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
WHERE rid.toode like '99000010%'
AND dok.kuupaev BETWEEN date'2008-11-21' AND date'2008-11-21'
"Aggregate (cost=17.86..17.87 rows=1 width=0) (actual
time=239346.647..239346.651 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..17.85 rows=1 width=0) (actual
time=3429.715..239345.923 rows=108 loops=1)"
" -> Nested Loop (cost=0.00..11.84 rows=1 width=24) (actual
time=3429.666..239339.687 rows=108 loops=1)"
" Join Filter: ("outer".dokumnr = "inner".dokumnr)"
" -> Index Scan using dok_kuupaev_idx on dok (cost=0.00..5.81
rows=1 width=4) (actual time=0.028..13.341 rows=1678 loops=1)"
" Index Cond: ((kuupaev >= '2008-11-21'::date) AND
(kuupaev <= '2008-11-21'::date))"
" -> Index Scan using rid_toode_pattern_idx on rid
(cost=0.00..6.01 rows=1 width=28) (actual time=0.025..86.156 rows=15402
loops=1678)"
" Index Cond: ((toode ~>=~ '99000010'::bpchar) AND (toode
~<~ '99000011'::bpchar))"
" Filter: (toode ~~ '99000010%'::text)"
" -> Index Scan using toode_pkey on toode (cost=0.00..6.00 rows=1
width=24) (actual time=0.032..0.037 rows=1 loops=108)"
" Index Cond: ("outer".toode = toode.toode)"
"Total runtime: 239347.132 ms"

explain analyze SELECT sum(1)
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
WHERE rid.toode like '99000010%'
AND dok.kuupaev = date'2008-11-21'
"Aggregate (cost=17.86..17.87 rows=1 width=0) (actual time=707.028..707.032
rows=1 loops=1)"
" -> Nested Loop (cost=0.00..17.85 rows=1 width=0) (actual
time=60.890..706.460 rows=108 loops=1)"
" -> Nested Loop (cost=0.00..11.84 rows=1 width=24) (actual
time=60.848..701.908 rows=108 loops=1)"
" -> Index Scan using rid_toode_pattern_idx on rid
(cost=0.00..6.01 rows=1 width=28) (actual time=0.120..247.636 rows=15402
loops=1)"
" Index Cond: ((toode ~>=~ '99000010'::bpchar) AND (toode
~<~ '99000011'::bpchar))"
" Filter: (toode ~~ '99000010%'::text)"
" -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..5.81
rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=15402)"
" Index Cond: (dok.dokumnr = "outer".dokumnr)"
" Filter: (kuupaev = '2008-11-21'::date)"
" -> Index Scan using toode_pkey on toode (cost=0.00..6.00 rows=1
width=24) (actual time=0.021..0.026 rows=1 loops=108)"
" Index Cond: ("outer".toode = toode.toode)"
"Total runtime: 707.250 ms"

vmstat 5 output during running slower query:

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id
wa
2 0 332 738552 0 1264832 0 0 4 1 1 11 6 1 83
10
1 0 332 738520 0 1264832 0 0 0 135 259 34 24 76 0
0
1 0 332 738488 0 1264832 0 0 0 112 263 42 24 76 0
0
1 0 332 738504 0 1264832 0 0 0 13 252 19 23 77 0
0
1 0 332 738528 0 1264832 0 0 0 31 255 26 26 74 0
0
1 0 332 738528 0 1264832 0 0 0 6 251 18 27 73 0
0
1 0 332 738544 0 1264856 0 0 5 22 254 25 27 73 0
0
1 0 332 737908 0 1264856 0 0 0 13 252 22 27 73 0
0
1 0 332 737932 0 1264856 0 0 0 2 251 18 23 77 0
0
1 0 332 737932 0 1264856 0 0 0 2 251 17 25 75 0
0
1 0 332 737932 0 1264856 0 0 0 4 252 19 25 75 0
0
1 0 332 737932 0 1264856 0 0 0 0 250 16 26 74 0
0
1 0 332 737932 0 1264856 0 0 0 8 252 19 26 74 0
0
1 0 332 737924 0 1264856 0 0 0 67 252 19 24 76 0
0
1 0 332 737900 0 1264856 0 0 0 13 258 37 25 75 0
0
1 0 332 737916 0 1264856 0 0 0 0 251 16 26 74 0
0
1 0 332 737932 0 1264856 0 0 0 2 251 18 26 74 0
0
1 1 332 736740 0 1264864 0 0 2 0 258 26 25 75 0
0
1 0 332 737716 0 1265040 0 0 10 91 267 60 28 72 0
0
1 0 332 737724 0 1265040 0 0 0 2 251 17 24 76 0
0
1 0 332 737732 0 1265044 0 0 1 219 288 128 24 76 0
0
r b swpd free buff cache si so bi bo in cs us sy id
wa
2 0 332 737732 0 1265044 0 0 0 20 255 25 23 77 0
0
1 0 332 737748 0 1265044 0 0 0 11 252 22 24 76 0
0
1 0 332 737748 0 1265044 0 0 0 0 250 16 24 76 0
0
1 0 332 737748 0 1265044 0 0 0 20 254 24 24 76 0
0
1 0 332 737740 0 1265044 0 0 0 87 252 20 26 74 0
0
1 0 332 737748 0 1265044 0 0 0 28 254 24 25 75 0
0
1 0 332 737748 0 1265052 0 0 2 6 251 18 27 73 0
0
1 0 332 737748 0 1265052 0 0 0 0 250 17 23 77 0
0
1 0 332 737748 0 1265052 0 0 0 2 251 17 26 74 0
0
1 0 332 737732 0 1265052 0 0 0 0 251 19 26 74 0
0
1 0 332 737732 0 1265052 0 0 0 1 251 17 25 75 0
0
1 0 332 737740 0 1265052 0 0 0 0 250 17 23 77 0
0
1 0 332 737748 0 1265052 0 0 0 0 250 16 24 76 0
0
1 0 332 737748 0 1265052 0 0 0 4 252 19 26 74 0
0
0 0 332 737740 0 1265052 0 0 0 0 252 20 12 37 51
0
0 0 332 737740 0 1265052 0 0 0 1 252 17 0 0
100 0 <-- query ends here probably
0 0 332 737740 0 1265052 0 0 0 4 251 18 0 0
100 0
0 0 332 734812 0 1265452 0 0 11 18 270 39 3 0 96
1
0 0 332 737172 0 1265632 0 0 18 153 261 35 1 0 98
1
0 0 332 737180 0 1265632 0 0 0 0 250 17 0 0
100 0
0 0 332 737188 0 1265632 0 0 0 0 251 16 0 0
100 0