Re: Increasing pattern index query speed - Mailing list pgsql-performance

From Andrus
Subject Re: Increasing pattern index query speed
Date
Msg-id 4936D68EF8C84087BBB9212C4019D5D8@andrusnotebook
Whole thread Raw
In response to Re: Increasing pattern index query speed  (Richard Huxton <dev@archonet.com>)
Responses Re: Increasing pattern index query speed  (Richard Huxton <dev@archonet.com>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: Craig Ringer
Date:
Subject: Re: many to many performance
Next
From: Richard Huxton
Date:
Subject: Re: Increasing pattern index query speed