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