Thread: Re: [pgsql-ru-general] GIN индекс - можно ли избавиться от Recheck?
2016-05-09 9:39 GMT+03:00 Dmitry E. Oboukhov <unera@debian.org>:
> имеется табличка
>
>> \d test
> Таблица "public.test"
> Колонка | Тип | Модификаторы
> ---------+---------+---------------------------------------------------
> id | integer | NOT NULL DEFAULT nextval('test_id_seq'::regclass)
> tags | text[] | NOT NULL
> Индексы:
> "test_pkey" PRIMARY KEY, btree (id)
>
>
> заполняем тестовую таблицу
>
>> INSERT INTO test
> SELECT
> generate_series(1, 10000000, 1) id,
> ARRAY[
> (ARRAY['abc', 'cde', 'def', 'ghi'])[1 + (random()*3)::INTEGER],
> (ARRAY['abc', 'cde', 'def', 'ghi'])[1 + (random()*3)::INTEGER]
> ] tags;
> INSERT 0 10000000
>
>> select count(*) FROM test;
> count
> ----------
> 10000000
> (1 строка)
>
> Строим GIN индекс
>
>> CREATE INDEX test_idx ON test USING GIN(tags);
> CREATE INDEX
>
Хочу добавить, что можно использовать COLLATION "C" для значительного
ускорения времени создания индекса.
CREATE INDEX test_idx ON test USING GIN(tags COLLATE "C");
CREATE INDEX
Time: 5800.975 ms
CREATE INDEX test_idx_ru ON test USING GIN(tags);
CREATE INDEX
Time: 19768.998 ms
У меня бывало и до 11 раз ускорение доходило. Правда придется потом
этот COLLATION "C" тащить и запросы, чтобы этот индекс использовался
:(
Раз в год на это натыкаюсь, все время забываем про это. Может к 9.7 не
забудем :)
>> EXPLAIN ANALYZE SELECT * FROM test WHERE tags @> ARRAY['abc', 'cde']::TEXT[];
> QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on test (cost=17511.01..131825.49 rows=1668518 width=41) (actual time=286.438..519.681
rows=1109868loops=1)
> Recheck Cond: (tags @> '{abc,cde}'::text[])
> Heap Blocks: exact=93458
> -> Bitmap Index Scan on test_idx (cost=0.00..17093.88 rows=1668518 width=0) (actual time=271.244..271.244
rows=1109868loops=1)
> Index Cond: (tags @> '{abc,cde}'::text[])
> Planning time: 0.665 ms
> Execution time: 552.225 ms
> (7 строк)
>
> В документации написано что Gin использует Recheck только когда
> используются веса, но тут никакие веса не используются.
> На recheck он потратил столько же времени сколько на выборку.
>
> можно ли от этого избавиться?
Recheck связан с bitmap-ом здесь. Analyzе всегда выдает recheck и надо
смотреть на строчку Heap Blocks: ...... В ваше случае recheck-а
реально не было, видно work_mem у вас приличного размера, так что
особенно оптимизировать нечего. А вот если у вас будет в этой строке
написано lossy=..., то тут и будет речек.
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera@debian.org jabber://UNera@uvw.ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlcwMKcACgkQq4wAz/jiZTcCfgCgiOlMaSZ/Fnz+P7f47wn8gXbp
> Px8AnRGaweMRvOOZ5epggb9E/Tdrhow/
> =XKEe
> -----END PGP SIGNATURE-----
>
Re: Re: [pgsql-ru-general] GIN индекс - можно ли избавиться от Recheck?
From
"Dmitry E. Oboukhov"
Date:
>>> EXPLAIN ANALYZE SELECT * FROM test WHERE tags @> ARRAY['abc', 'cde']::TEXT[];
>> QUERY PLAN
>>
-------------------------------------------------------------------------------------------------------------------------------------
>> Bitmap Heap Scan on test (cost=17511.01..131825.49 rows=1668518 width=41) (actual time=286.438..519.681
rows=1109868loops=1)
>> Recheck Cond: (tags @> '{abc,cde}'::text[])
>> Heap Blocks: exact=93458
->>> Bitmap Index Scan on test_idx (cost=0.00..17093.88 rows=1668518 width=0) (actual time=271.244..271.244
rows=1109868loops=1)
>> Index Cond: (tags @> '{abc,cde}'::text[])
>> Planning time: 0.665 ms
>> Execution time: 552.225 ms
>> (7 строк)
>>
>> В документации написано что Gin использует Recheck только когда
>> используются веса, но тут никакие веса не используются.
>> На recheck он потратил столько же времени сколько на выборку.
>>
>> можно ли от этого избавиться?
> Recheck связан с bitmap-ом здесь. Analyzе всегда выдает recheck и надо
> смотреть на строчку Heap Blocks: ...... В ваше случае recheck-а
> реально не было, видно work_mem у вас приличного размера, так что
> особенно оптимизировать нечего. А вот если у вас будет в этой строке
> написано lossy=..., то тут и будет речек.
эм я тогда не очень понимаю EXPLAIN.
он пишет что индекс скан у него закончился на 271 милисекунде.
а потом речек и битмап скан. который закончился на 519 милисекунде.
если бы речек был то у него свое время было бы прописано?
так?
--
. ''`. Dmitry E. Oboukhov
: :’ : email: unera@debian.org jabber://UNera@uvw.ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
Attachment
Re: [pgsql-ru-general] Re: [pgsql-ru-general] GIN индекс - можно ли избавиться от Recheck?
From
Oleg Bartunov
Date:
2016-05-10 10:17 GMT+03:00 Dmitry E. Oboukhov <unera@debian.org>:
>>>> EXPLAIN ANALYZE SELECT * FROM test WHERE tags @> ARRAY['abc', 'cde']::TEXT[];
>>> QUERY PLAN
>>>
-------------------------------------------------------------------------------------------------------------------------------------
>>> Bitmap Heap Scan on test (cost=17511.01..131825.49 rows=1668518 width=41) (actual time=286.438..519.681
rows=1109868loops=1)
>>> Recheck Cond: (tags @> '{abc,cde}'::text[])
>>> Heap Blocks: exact=93458
> ->>> Bitmap Index Scan on test_idx (cost=0.00..17093.88 rows=1668518 width=0) (actual time=271.244..271.244
rows=1109868loops=1)
>>> Index Cond: (tags @> '{abc,cde}'::text[])
>>> Planning time: 0.665 ms
>>> Execution time: 552.225 ms
>>> (7 строк)
>>>
>>> В документации написано что Gin использует Recheck только когда
>>> используются веса, но тут никакие веса не используются.
>>> На recheck он потратил столько же времени сколько на выборку.
>>>
>>> можно ли от этого избавиться?
>
>> Recheck связан с bitmap-ом здесь. Analyzе всегда выдает recheck и надо
>> смотреть на строчку Heap Blocks: ...... В ваше случае recheck-а
>> реально не было, видно work_mem у вас приличного размера, так что
>> особенно оптимизировать нечего. А вот если у вас будет в этой строке
>> написано lossy=..., то тут и будет речек.
>
> эм я тогда не очень понимаю EXPLAIN.
> он пишет что индекс скан у него закончился на 271 милисекунде.
> а потом речек и битмап скан. который закончился на 519 милисекунде.
вы не обратили внимание, что bitmap heap scan - оставшееся время он
дергал табличку (heap).
>
> если бы речек был то у него свое время было бы прописано?
> так?
нет, просто было бы написано примерно так (см. lossy=)
explain analyze select count(*) from pgin where fts @@
to_tsquery('russian','tom & lane');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=112493.60..112493.61 rows=1 width=8) (actual
time=7971.988..7971.988 rows=1 loops=1)
-> Bitmap Heap Scan on pgin (cost=536.29..112359.32 rows=53714
width=0) (actual time=81.647..7941.289 rows=214852 loops=1)
Recheck Cond: (fts @@ '''tom'' & ''lane'''::tsquery)
Rows Removed by Index Recheck: 213757
Heap Blocks: exact=51700 lossy=52901
-> Bitmap Index Scan on pgin_rum_fts_idx (cost=0.00..522.86
rows=53714 width=0) (actual time=70.485..70.485 rows=214852 loops=1)
Index Cond: (fts @@ '''tom'' & ''lane'''::tsquery)
Planning time: 0.239 ms
Execution time: 7972.052 ms
(9 rows)
увеличиваем work_mem
set work_mem to '128MB';
и речек собственно пропадает
explain analyze select count(*) from pgin where fts @@
to_tsquery('russian','tom & lane');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=112493.60..112493.61 rows=1 width=8) (actual
time=477.556..477.556 rows=1 loops=1)
-> Bitmap Heap Scan on pgin (cost=536.29..112359.32 rows=53714
width=0) (actual time=109.852..461.347 rows=214852 loops=1)
Recheck Cond: (fts @@ '''tom'' & ''lane'''::tsquery)
Heap Blocks: exact=104601
-> Bitmap Index Scan on pgin_rum_fts_idx (cost=0.00..522.86
rows=53714 width=0) (actual time=73.993..73.993 rows=214852 loops=1)
Index Cond: (fts @@ '''tom'' & ''lane'''::tsquery)
Planning time: 0.348 ms
Execution time: 477.720 ms
(8 rows)
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera@debian.org jabber://UNera@uvw.ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlcxivwACgkQq4wAz/jiZTcQjACdEOln1cWzN5dvb/v3d5KlGm70
> Wb8Ani7oanxjwR8lyOhLra+hh6Ue9PzM
> =k0Xg
> -----END PGP SIGNATURE-----
>