Запрос PostgreSQL занимает очень много времени

У меня есть таблица с 3 столбцами и составным первичным ключом со всеми 3 столбцами. Все отдельные столбцы имеют много дубликатов, и у меня есть btree отдельно для всех из них. В таблице около 10 миллионов записей. Мой запрос только с условием с жестко заданным значением для одного столбца всегда будет возвращать более миллиона записей. Это занимает более 40 секунд, тогда как это занимает очень мало секунд, если я ограничиваю запрос 1 или 2 миллионами строк без каких-либо условий.

Любая помощь в оптимизации, поскольку в Postgres нет растрового индекса? Все 3 столбца имеют много дубликатов, поможет ли мне удалить на них индекс btree?

SELECT t1.filterid,
       t1.filterby,
       t1.filtertype 
FROM echo_sm.usernotificationfilters t1 
WHERE t1.filtertype = 9 
UNION 
SELECT t1.filterid, '-1' AS filterby, 9 AS filtertype 
FROM echo_sm.usernotificationfilters t1 
WHERE NOT EXISTS  (SELECT 1
                   FROM echo_sm.usernotificationfilters t2
                   WHERE t2.filtertype = 9 AND t2.filterid = t1.filterid);

Столбец типа фильтра является целым числом, а остальные 2 — varchar (50). Все 3 столбца имеют отдельные индексы btree.

Объясните план:

Unique  (cost=2168171.15..2201747.47 rows=3357632 width=154) (actual time=32250.340..36371.928 rows=3447159 loops=1)
  ->  Sort  (cost=2168171.15..2176565.23 rows=3357632 width=154) (actual time=32250.337..35544.050 rows=4066447 loops=1)
        Sort Key: usernotificationfilters.filterid, usernotificationfilters.filterby, usernotificationfilters.filtertype
        Sort Method: external merge  Disk: 142696kB
        ->  Append  (cost=62854.08..1276308.41 rows=3357632 width=154) (actual time=150.155..16025.874 rows=4066447 loops=1)
              ->  Bitmap Heap Scan on usernotificationfilters  (cost=62854.08..172766.46 rows=3357631 width=25) (actual time=150.154..574.297 rows=3422522 loops=1)
                    Recheck Cond: (filtertype = 9)
                    Heap Blocks: exact=39987
                    ->  Bitmap Index Scan on index_sm_usernotificationfilters_filtertype  (cost=0.00..62014.67 rows=3357631 width=0) (actual time=143.585..143.585 rows=3422522 loops=1)
                          Index Cond: (filtertype = 9)
              ->  Gather  (cost=232131.85..1069965.63 rows=1 width=50) (actual time=3968.492..15133.812 rows=643925 loops=1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Hash Anti Join  (cost=231131.85..1068965.53 rows=1 width=50) (actual time=4135.235..12945.029 rows=214642 loops=3)
                          Hash Cond: ((usernotificationfilters_1.filterid)::text = (usernotificationfilters_1_1.filterid)::text)
                          ->  Parallel Seq Scan on usernotificationfilters usernotificationfilters_1  (cost=0.00..106879.18 rows=3893718 width=14) (actual time=0.158..646.432 rows=3114974 loops=3)
                          ->  Hash  (cost=172766.46..172766.46 rows=3357631 width=14) (actual time=4133.991..4133.991 rows=3422522 loops=3)
                                Buckets: 131072  Batches: 64  Memory Usage: 3512kB
                                ->  Bitmap Heap Scan on usernotificationfilters usernotificationfilters_1_1  (cost=62854.08..172766.46 rows=3357631 width=14) (actual time=394.775..1891.931 rows=3422522 loops=3)
                                      Recheck Cond: (filtertype = 9)
                                      Heap Blocks: exact=39987
                                      ->  Bitmap Index Scan on index_sm_usernotificationfilters_filtertype  (cost=0.00..62014.67 rows=3357631 width=0) (actual time=383.635..383.635 rows=3422522 loops=3)
                                            Index Cond: (filtertype = 9)
Planning time: 0.467 ms
Execution time: 36531.763 ms

postgresql postgresql-10 postgresql-performance
person Manoharan    schedule 09.12.2019    source источник
comment
поскольку в Postgres нет растрового индекса — Postgres может использовать обычные индексы как растровые индексы, используя сканирование растрового индекса   -  person a_horse_with_no_name    schedule 09.12.2019
comment
My query with just a condition with a hardcoded value for single column would always return more than a million records.‹‹-- существует ли какая-либо комбинация столбцов, которая может быть ключом-кандидатом? У вас есть первичный ключ?   -  person joop    schedule 09.12.2019
comment
@joop Да, есть составной первичный ключ — комбинация всех трех столбцов в таблице.   -  person Manoharan    schedule 09.12.2019
comment
Я подозреваю, что нет суперэффективного способа делать то, что вы хотите, с вашей текущей моделью данных. Мы могли бы сделать это в 2 или 3 раза быстрее, а не в 10 или 100 раз быстрее. Возможно, вам потребуется изменить модель данных.   -  person jjanes    schedule 09.12.2019

Ответы (3)


arrow_upward
1
arrow_downward

Второй подзапрос в вашем UNION занимает около 15 секунд сам по себе, и его можно было бы оптимизировать отдельно от остальной части запроса.

Сортировка для реализации удаления дубликатов, подразумеваемая UNION, сама по себе занимает около 20 секунд. Сливается на диск. Вы можете увеличивать «work_mem» до тех пор, пока он либо не перестанет проливаться на диск, либо не начнет использовать хеш, а не сортировку. Конечно, вам нужно иметь оперативную память для резервного копирования вашей настройки «work_mem».

Третья возможность заключается в том, чтобы не рассматривать эти этапы изолированно. Если бы у вас был индекс, который позволял бы читать данные из 2-й ветви объединения уже в порядке, то, возможно, не пришлось бы пересортировать все это. Вероятно, это будет индекс (filterid, filterby, filtertype).

person jjanes    schedule 09.12.2019
comment
Для этих трех столбцов уже существует неявно созданный индекс, поскольку они образуют составной первичный ключ. Меня беспокоит то, что в таблице 9,5 миллионов строк, и простой выбор * из таблицы или выбор фильтра по таблице занимает от 15 до 20 секунд. Уже есть индексы, и я также сделал вакуум. Как его оптимизировать, так как это займет больше времени, если я добавлю условия или соединения. В чем может быть причина такой медлительности? - person Manoharan; 09.12.2019
comment
@Манохаран. Как выглядит план с set enable_seqscan=off? - person jjanes; 09.12.2019
comment
То же самое с установкой set enable_seqscan=off' - person Manoharan; 10.12.2019

arrow_upward
1
arrow_downward

Это отдельный независимый способ приблизиться к нему.

я думаю твой

WHERE NOT EXISTS  (SELECT 1...

можно было бы правильно изменить на

WHERE t1.filtertype <> 9 NOT EXISTS AND (SELECT 1...

потому что случай, когда t1.filtertype=9, отфильтрует себя. Это правильно? Если это так, вы можете попробовать написать это таким образом, так как планировщик, вероятно, недостаточно умен, чтобы сделать это преобразование самостоятельно. Как только вы это сделаете, вам, возможно, пригодится отфильтрованный индекс, подобный приведенному ниже.

create index on echo_sm.usernotificationfilters (filterid, filterby, filtertype)
   where filtertype <> 9

Но, если вы не избавитесь от этого или не ускорите его, вы не сможете добиться значительных улучшений с другими вещами.

person jjanes    schedule 09.12.2019
comment
Это условие нельзя использовать, так как подзапрос после NOT EXISTS должен соответствовать обоим условиям, а основной запрос — нет. - person Manoharan; 10.12.2019

arrow_upward
1
arrow_downward

Похоже, вы хотите получить только одну запись для каждого filterid: запись с filtertype = 9, если она доступна, или просто другую, с фиктивными значениями для других столбцов. Это можно сделать, заказав BY (filtertype<>9), filtertype ) и выбрав только первую строку через row_number() = 1:

-- EXPLAIN ANALYZE
SELECT xx.filterid
        , case(xx.filtertype) when 9 then xx.filterby ELSE '-1' END AS filterby
        , 9 AS filtertype -- xx.filtertype
        -- , xx.rn
FROM (
        SELECT t1.filterid , t1.filterby , t1.filtertype
        , row_number() OVER (PARTITION BY t1.filterid ORDER BY (filtertype<>9), filtertype ) AS rn
        FROM userfilters t1
        ) xx
WHERE xx.rn = 1
-- ORDER BY xx.filterid, xx.rn
        ;

Этот запрос может поддерживаться индексом того же выражения:

CREATE INDEX ON userfilters ( filterid , (filtertype<>9), filtertype ) ;

Но на моей машине версия UNION ALL работает быстрее (с тем же индексом):

EXPLAIN ANALYZE
SELECT t1.filterid
        , t1.filterby
        , t1.filtertype
FROM userfilters t1
WHERE t1.filtertype = 9
UNION ALL
SELECT DISTINCT t1.filterid , '-1' AS filterby ,9 AS filtertype
FROM userfilters t1
WHERE NOT EXISTS (
        SELECT *
        FROM userfilters t2
        WHERE t2.filtertype = 9 AND t2.filterid = t1.filterid
        )
        ;

Еще проще (и быстрее!) использовать DISTINCT ON(), поддерживаемый тем же условным индексом:

-- EXPLAIN ANALYZE
SELECT DISTINCT ON (t1.filterid)
         t1.filterid
        , case(t1.filtertype) when 9 then t1.filterby ELSE '-1' END AS filterby
        , 9 AS filtertype -- t1.filtertype
FROM userfilters t1
ORDER BY t1.filterid , (t1.filtertype<>9), t1.filtertype
        ;
person wildplasser    schedule 11.12.2019