@Maxim Boguk Спасибо за ответ!
Как ни странно именно v10 исходя из статистики ведет себя корректно. :)
Не могу согласится - на реальных данных (а не жалком 1М строк) запрос на 10-м отрабатывает ~800 секунд
а на 9-м меньше 1 секунды
(но результаты explain не отличаются - индекс не используется на 10-ке)
Уточню, что я создала лабу из 2-х линуксов с 9-м и 10-м постгресом и залила туда 1М записей из оригинальной таблицы что б сравнить поведение. На самом деле таблица значительно больше (67 гиг данных)
Собственно сама задача поиска проблемы возникла из-за замедления приложения после перехода на 10-ку
Покажите итоги на v10 следующих запросов
На 9 и на 10 вывод одинаковый
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
explain analyze select * from articles where type='Tweet';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on articles (cost=0.00..112464.54 rows=900568 width=2270) (actual time=4.904..6283.272 rows=902048 loops=1)
Filter: ((type)::text = 'Tweet'::text)
Rows Removed by Filter: 97952
Planning time: 0.238 ms
Execution time: 6347.023 ms
(5 rows)
[SRC sql]
explain analyze select * from articles where masked_url_md5 IS NULL;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on articles (cost=0.00..109965.83 rows=370575 width=2270) (actual time=3.447..2085.110 rows=373613 loops=1)
Filter: (masked_url_md5 IS NULL)
Rows Removed by Filter: 626387
Planning time: 1.312 ms
Execution time: 2108.809 ms
(5 rows)
[SRC sql]
explain analyze select * from articles where masked_url_md5 IS NULL AND type='Tweet';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on articles (cost=0.00..112464.54 rows=333900 width=2270) (actual time=7.074..2132.089 rows=334019 loops=1)
Filter: ((masked_url_md5 IS NULL) AND ((type)::text = 'Tweet'::text))
Rows Removed by Filter: 665981
Planning time: 0.257 ms
Execution time: 2152.956 ms
(5 rows)
v1: индекс на articles(type) where masked_url_md5 IS NULL с последующим analyze articles
1.
2.
CREATE INDEX eg_1 ON public.articles USING btree (type) where masked_url_md5 IS NULL;
CREATE INDEX
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
EXPLAIN ANALYZE SELECT "articles".* FROM "articles" WHERE "articles"."type" IN ('Tweet') AND "articles"."duplicates_mapped" = 't' AND ("articles"."masked_url_md5" IS NOT NULL) AND ("articles"."proc_phase" != 'culled') AND ("articles"."type" != 'InfoItem') AND "articles"."duplicate" = 'f' AND "articles"."masked_url_md5" IS NULL LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.58 rows=1 width=2416) (actual time=542.519..542.519 rows=0 loops=1)
-> Seq Scan on articles (cost=0.00..117458.00 rows=45474 width=2416) (actual time=542.517..542.517 rows=0 loops=1)
Filter: (duplicates_mapped AND (masked_url_md5 IS NOT NULL) AND (NOT duplicate) AND (masked_url_md5 IS NULL) AND ((proc_phase)::text <> 'culled'::text) AND ((type)::text <> 'InfoItem'::text) AND ((type)::text = 'Tweet'::text))
Rows Removed by Filter: 1000000
Planning time: 0.403 ms
Execution time: 542.559 ms
(6 rows)
Т.е насколько можно судить индекс все так же не используется.
или если v1 не помогает то использовать фичу 10 с create statistics:
CREATE STATISTICS ON type, masked_url_md5 FROM articles ;
с последующим analyze articles И проверкой вашего запроса.
1.
2.
3.
4.
# CREATE STATISTICS eg_1 ON type, masked_url_md5 FROM articles;
CREATE STATISTICS
# analyze articles;
ANALYZE
Тут видно что запрос действительно медленный :
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
EXPLAIN ANALYZE SELECT "articles".* FROM "articles" WHERE "articles"."type" IN ('Tweet') AND "articles"."duplicates_mapped" = 't' AND ("articles"."masked_url_md5" IS NOT NULL) AND ("articles"."proc_phase" != 'culled') AND ("articles"."type" != 'InfoItem') AND "articles"."duplicate" = 'f' AND "articles"."masked_url_md5" IS NULL LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.58 rows=1 width=2416) (actual time=525.965..525.965 rows=0 loops=1)
-> Seq Scan on articles (cost=0.00..117458.00 rows=45474 width=2416) (actual time=525.963..525.963 rows=0 loops=1)
Filter: (duplicates_mapped AND (masked_url_md5 IS NOT NULL) AND (NOT duplicate) AND (masked_url_md5 IS NULL) AND ((proc_phase)::text <> 'culled'::text) AND ((type)::text <> 'InfoItem'::text) AND ((type)::text = 'Tweet'::text))
Rows Removed by Filter: 1000000
Planning time: 0.438 ms
Execution time: 526.010 ms
(6 rows)
Для сравнения с 9-ки
1.
2.
3.
4.
5.
6.
7.
8.
9.
EXPLAIN ANALYZE SELECT "articles".* FROM "articles" WHERE "articles"."type" IN ('Tweet') AND "articles"."duplicates_mapped" = 't' AND ("articles"."masked_url_md5" IS NOT NULL) AND ("articles"."proc_phase" != 'culled') AND ("articles"."type" != 'InfoItem') AND "articles"."duplicate" = 'f' AND "articles"."masked_url_md5" IS NULL LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..1.40 rows=1 width=2270) (actual time=0.003..0.003 rows=0 loops=1)
-> Index Scan using new_articles_proc_phase_type_duplicates_mapped_duplicate_ma_idx on articles (cost=0.42..43172.61 rows=44131 width=2270) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (((type)::text = 'Tweet'::text) AND (duplicates_mapped = true) AND (duplicate = false) AND (masked_url_md5 IS NOT NULL) AND (masked_url_md5 IS NULL))
Planning time: 0.303 ms
Execution time: 0.038 ms
(5 rows)
Проблема в следующем на самом деле:
-> Bitmap Index Scan on new_articles_proc_phase_type_duplicates_mapped_duplicate_ma_idx (cost=0.00..4833.77 rows=45129 width=0) (actual time=123.466..123.466 rows=0 loops=1)
Index Cond: (((type)::text = 'Tweet'::text) AND (masked_url_md5 IS NULL))
И исходя из статистики 10 поступает более верно... быстрее найти seq scanом 1 строку из 50k в таблице на 1M (это всего 20 строк проверить в среднем) чем лезть в индекс.
Это пояснение мне к сожалению не ясно.
Дядя, я не настоящий сварщик (с) (я DBA поневоле - надо решить задачу переезда и опыт с постгресом очень небольшой)
>быстрее найти seq scanом 1
Это полный перебор, разве нет? Т.е возможно я не верно интерпретирую то что показывает EXPLAIN
Я читала вывод так "планировщик считает что для 1М записей он найдет то 1 что требуется по лимиту и соответствует условию просто перебором в лоб (так как ему нужна всего одна запись)"
Но так как по факту в таблице таких записей нет - то запрос фактически исполняется медленно и эквивалентен "SELECT "articles".* FROM "articles";"
Можете пожалуйста пояснить подробнее (или дать ссылку на правильное место в документации)
