На aws установлен postgres 9.6. Имеется в базе табличка posts с 2,5 млн записей. В табличке есть колонка колнка text[] tags. На колонку tags установлен gin-индекс.
Простой запрос c фильтром по элементу массива tags выполняется неадекватно долго:
explain analyze
SELECT count(*)
FROM posts
WHERE ARRAY ['love'::text] <@ "tags"
"Aggregate (cost=235726.85..235726.86 rows=1 width=8) (actual time=116389.531..116389.532 rows=1 loops=1)"
" -> Bitmap Heap Scan on posts (cost=4854.04..234939.10 rows=315102 width=0) (actual time=358.306..116243.970 rows=340759 loops=1)"
" Recheck Cond: ('{love}'::text[] <@ tags)"
" Heap Blocks: exact=159677"
" -> Bitmap Index Scan on idx_tags (cost=0.00..4775.26 rows=315102 width=0) (actual time=318.699..318.699 rows=340910 loops=1)"
" Index Cond: ('{love}'::text[] <@ tags)"
"Planning time: 1.014 ms"
"Execution time: 116390.525 ms"
Большую часть времени запрос выполняет Bitmap Heap Scan on posts, хотя по сути для такого запроса достаточно только работы с индексом. Какие могут быть причины такой долгой обработки? Как можно оптимизировать запрос, чтобы использовался только индекс?
Или это нормальное время для 300 тыс записей, после выборки по индексу и нужно думать в сторону реорганизации таблицы (партицирование например)? Спасибо!
сделал дамп и накатил на
сделал дамп и накатил на своем локальном стенде. Запрос выполнился за 3 секунды. Теперь еще более непонятно, в чем может быть проблема. На AWS используются ssd-диски, предела по IIOPS не достигается...
Надо привести explain analyze
Надо привести explain analyze и там, где 3 секунды. Я догадываюсь почему так.
А вообще по ситуации вроде всё прозрачно:
Вот за счёт второго пункта и выходит такое время ожидания.
Вариант решения: какой-то другой индекс использовать? Или агрегировать данные в отдельной таблице, и брать уже оттуда.
explain analyze 3 секунды
"Aggregate (cost=221491.58..221491.59 rows=1 width=8) (actual time=2465.607..2465.609 rows=1 loops=1)"
" -> Bitmap Heap Scan on posts (cost=3413.14..220571.21 rows=368147 width=0) (actual time=153.381..1909.497 rows=362432 loops=1)"
" Recheck Cond: ('{love}'::text[] <@ tags)"
" Heap Blocks: exact=163729"
" -> Bitmap Index Scan on idx_tags (cost=0.00..3321.11 rows=368147 width=0) (actual time=89.957..89.957 rows=362432 loops=1)"
" Index Cond: ('{love}'::text[] <@ tags)"
"Planning time: 0.359 ms"
"Execution time: 2466.147 ms"
привожу explain analyze локального стенда по дампу базы из aws. Не понятно почему такая огромная разница в скорости выполнения Bitmap Heap Scan
без индекса быстрее!!
Для эксперимента удалил индекс на поле tags. Запрос выполнился в несколько раз быстрее!
С GIN-индексом:
"Aggregate (cost=293918.85..293918.86 rows=1 width=8) (actual time=132098.691..132098.691 rows=1 loops=1)"
" -> Bitmap Heap Scan on posts (cost=5685.85..292888.57 rows=412109 width=0) (actual time=343.147..131930.811 rows=445380 loops=1)"
" Recheck Cond: ('{love}'::text[] <@ tags)"
" Heap Blocks: exact=206714"
" -> Bitmap Index Scan on idx_tags (cost=0.00..5582.82 rows=412109 width=0) (actual time=286.241..286.241 rows=446297 loops=1)"
" Index Cond: ('{love}'::text[] <@ tags)"
"Planning time: 0.204 ms"
"Execution time: 132099.867 ms"
Без gin-индекса:
"Aggregate (cost=310682.95..310682.96 rows=1 width=8) (actual time=27888.581..27888.582 rows=1 loops=1)"
" -> Seq Scan on posts (cost=0.00..309652.67 rows=412109 width=0) (actual time=4.206..27790.823 rows=445380 loops=1)"
" Filter: ('{love}'::text[] <@ tags)"
" Rows Removed by Filter: 3134200"
"Planning time: 0.171 ms"
"Execution time: 27888.610 ms"
Выходит, на текстовые массивы gin-индекс лучше не применять?!