Increasing pattern index query speed

Поиск
Список
Период
Сортировка
От Andrus
Тема Increasing pattern index query speed
Дата
Msg-id gg9pg5$1edv$1@news.hub.org
обсуждение исходный текст
Ответы Re: Increasing pattern index query speed
Список pgsql-performance
Both queries return same result (19) and return same data.
Pattern query is a much slower (93 sec) than  equality check (13 sec).
How to fix this ?
Using 8.1.4, utf-8 encoding, et-EE locale.

Andrus.

SELECT sum(1)
   FROM dok JOIN rid USING (dokumnr)
   JOIN toode USING (toode)
   WHERE rid.toode = '99000010' AND dok.kuupaev BETWEEN '2008-11-21' AND
'2008-11-21'
          AND dok.yksus  LIKE 'ORISSAARE%'

"Aggregate  (cost=43.09..43.10 rows=1 width=0) (actual
time=12674.675..12674.679 rows=1 loops=1)"
"  ->  Nested Loop  (cost=29.57..43.08 rows=1 width=0) (actual
time=2002.045..12673.645 rows=19 loops=1)"
"        ->  Nested Loop  (cost=29.57..37.06 rows=1 width=24) (actual
time=2001.922..12672.344 rows=19 loops=1)"
"              ->  Index Scan using dok_kuupaev_idx on dok  (cost=0.00..3.47
rows=1 width=4) (actual time=342.812..9810.627 rows=319 loops=1)"
"                    Index Cond: ((kuupaev >= '2008-11-21'::date) AND
(kuupaev <= '2008-11-21'::date))"
"                    Filter: (yksus ~~ 'ORISSAARE%'::text)"
"              ->  Bitmap Heap Scan on rid  (cost=29.57..33.58 rows=1
width=28) (actual time=8.948..8.949 rows=0 loops=319)"
"                    Recheck Cond: (("outer".dokumnr = rid.dokumnr) AND
(rid.toode = '99000010'::bpchar))"
"                    ->  BitmapAnd  (cost=29.57..29.57 rows=1 width=0)
(actual time=8.930..8.930 rows=0 loops=319)"
"                          ->  Bitmap Index Scan on rid_dokumnr_idx
(cost=0.00..2.52 rows=149 width=0) (actual time=0.273..0.273 rows=2
loops=319)"
"                                Index Cond: ("outer".dokumnr =
rid.dokumnr)"
"                          ->  Bitmap Index Scan on rid_toode_idx
(cost=0.00..26.79 rows=1941 width=0) (actual time=8.596..8.596 rows=15236
loops=319)"
"                                Index Cond: (toode = '99000010'::bpchar)"
"        ->  Index Scan using toode_pkey on toode  (cost=0.00..6.01 rows=1
width=24) (actual time=0.043..0.048 rows=1 loops=19)"
"              Index Cond: ('99000010'::bpchar = toode)"
"Total runtime: 12675.191 ms"

explain analyze SELECT sum(1)
   FROM dok JOIN rid USING (dokumnr)
   JOIN toode USING (toode)
   WHERE rid.toode like '99000010%' AND dok.kuupaev BETWEEN '2008-11-21' AND
'2008-11-21'
          AND dok.yksus  LIKE 'ORISSAARE%'


"Aggregate  (cost=15.52..15.53 rows=1 width=0) (actual
time=92966.501..92966.505 rows=1 loops=1)"
"  ->  Nested Loop  (cost=0.00..15.52 rows=1 width=0) (actual
time=24082.032..92966.366 rows=19 loops=1)"
"        ->  Nested Loop  (cost=0.00..9.50 rows=1 width=24) (actual
time=24081.919..92965.116 rows=19 loops=1)"
"              Join Filter: ("outer".dokumnr = "inner".dokumnr)"
"              ->  Index Scan using dok_kuupaev_idx on dok  (cost=0.00..3.47
rows=1 width=4) (actual time=0.203..13924.324 rows=319 loops=1)"
"                    Index Cond: ((kuupaev >= '2008-11-21'::date) AND
(kuupaev <= '2008-11-21'::date))"
"                    Filter: (yksus ~~ 'ORISSAARE%'::text)"
"              ->  Index Scan using rid_toode_pattern_idx on rid
(cost=0.00..6.01 rows=1 width=28) (actual time=0.592..166.778 rows=15235
loops=319)"
"                    Index Cond: ((toode ~>=~ '99000010'::bpchar) AND (toode
~<~ '99000011'::bpchar))"
"                    Filter: (toode ~~ '99000010%'::text)"
"        ->  Index Scan using toode_pkey on toode  (cost=0.00..6.01 rows=1
width=24) (actual time=0.041..0.046 rows=1 loops=19)"
"              Index Cond: ("outer".toode = toode.toode)"
"Total runtime: 92967.512 ms"


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Andrus"
Дата:
Сообщение: seq scan over 3.3 million rows instead of single key index access
Следующее
От: Glyn Astill
Дата:
Сообщение: Re: Perc 3 DC