У меня возникли проблемы с тем, чтобы postgres использовал мои многоколоночные индексы для полного поиска с использованием расширения btree_gin. Это страница поиска статей. Идея использования btree_gin заключается в том, чтобы получить поле 'id' для сортировки и magazine_id в качестве фильтра:
CREATE INDEX idx_gin_search ON article USING gin(id, magazine_id, search_vector_full) WITH (fastupdate = off);
Postgres решает вместо этого использовать индекс btree в журнале, а затем фильтровать (= медленно):
Executed SQL
SELECT ••• FROM article WHERE (( (article.search_vector) @@
(plainto_tsquery('pg_catalog.english', 'interesting'))) AND
article.magazine_id = 7) ORDER BY article.id ASC LIMIT 36
Time 13.4780406952 ms
QUERY PLAN
Limit (cost=2021.87..2021.96 rows=36 width=384) (actual time=9.782..9.787 rows=36 loops=1)
-> Sort (cost=2021.87..2027.49 rows=2248 width=384) (actual time=9.781..9.784 rows=36 loops=1)
Sort Key: id
Sort Method: top-N heapsort Memory: 53kB
-> Index Scan using idx_magazine_id on article (cost=0.29..1952.53 rows=2248 width=384) (actual time=0.035..8.924 rows=2249 loops=1)
Index Cond: (magazine_id = 7)
Filter: (search_vector @@ '''interesting'''::tsquery)
Rows Removed by Filter: 11413
Planning time: 4.600 ms
Execution time: 9.860 ms
Затем, что я нахожу даже менее понимающим, так это то, что он также отказывается использовать этот простой индекс btree на странице LIST для статей, где они просто перечислены x на странице в порядке убывания:
CREATE INDEX idx_btree_listing ON article USING btree(id DESC, magazine_id);
Опять же, он не использует индекс с несколькими столбцами:
Executed SQL
SELECT ••• FROM article WHERE article.magazine_id = 7
ORDER BY article.id DESC LIMIT 36
Time 1.4750957489 ms
QUERY PLAN
Limit (cost=0.29..7.48 rows=36 width=384) (actual time=0.034..0.115 rows=36 loops=1)
-> Index Scan Backward using idx_magazine_id on article (cost=0.29..2729.56 rows=13662 width=384) (actual time=0.031..0.107 rows=36 loops=1)
Filter: (magazine_id = 7)
Planning time: 1.354 ms
Execution time: 0.207 ms
РЕДАКТИРОВАТЬ: Вышеупомянутая установка разработки с меньшим количеством записей и только 1 магазином, следовательно, высокая скорость. Вот журнал, созданный auto_explain на рабочем сервере:
duration: 230.629 ms plan:
SELECT article.id, article.title, article.date, article.content FROM article WHERE article.magazine_id = 7 ORDER BY article.id DESC LIMIT 36
Limit (cost=0.42..43.67 rows=36 width=306) (actual time=229.876..229.995 rows=36 loops=1)
-> Index Scan Backward using idx_magazine_id on article (cost=0.42..239539.22 rows=199379 width=306) (actual time=229.866..229.968 rows=36 loops=1)
Filter: (article.magazine_id = 7)
Rows Removed by Filter: 116414
Я был бы признателен, если бы кто-нибудь мог дать мне советы по дальнейшей отладке этого.
mmxxx_sitevideo_pkey
- при времени выполнения 0,2 миллисекунды мне это кажется достаточно быстрым. Время планирования заняло гораздо больше времени, но общее время выполнения по-прежнему составляет всего 1,5 милли секунды. Какую производительность вы ожидаете? Насколько быстро вам это нужно? Хотя я должен признать, что сроки планирования (особенно первого) кажутся довольно высокими для таких простых утверждений. - person a_horse_with_no_name   schedule 02.02.2016explain (analyze, verbose)
из производства. - person a_horse_with_no_name   schedule 02.02.2016