Запрос с соединением выполняется слишком долго — Postgresql

У меня есть запрос, который выполняется слишком долго. Я использую PostgreSQL 10.3. В моих таблицах, задействованных в этом запросе, у меня около 3,5 миллионов записей в каждой. Запрос:

SELECT thf.attr1, thf.attr2, thf.attr3, thf.attr4
FROM tb_one AS thf
INNER JOIN tb_two AS ths
ON ths.tb_hit_hitid = thf.tb_hit_hitid
WHERE ths.source IN ('source1', 'source2')

В этих таблицах у меня есть индекс:

CREATE INDEX tb_two_idx_1 on tb_two (Source ASC, attr5 ASC);
CREATE INDEX tb_one_idx_1 on tb_one USING btree (attr1 ASC,attr2 ASC,attr3 ASC,attr4 ASC);
CREATE INDEX tb_one_idx_2 on tb_hit_feature (tb_hit_HitId ASC);
CREATE INDEX tb_two_idx_2 on tb_hit_source (tb_hit_HitId ASC);

Это ПЛАН ЗАПРОСА (explain (analyse, buffers)):

    QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=3.85..287880.35 rows=1771004 width=44) (actual time=0.091..3894.024 rows=1726970 loops=1)
   Merge Cond: (thf.tb_hit_hitid = ths.tb_hit_hitid)
   Buffers: shared hit=354821
   ->  Index Scan using tb_one_idx_2 on tb_one thf  (cost=0.43..124322.43 rows=3230800 width=52) (actual time=0.014..655.036 rows=1726946 loops=1)
         Buffers: shared hit=27201
   ->  Index Scan using tb_two_idx_2 on tb_two ths  (cost=0.43..139531.97 rows=1771004 width=8) (actual time=0.069..1604.789 rows=1726973 loops=1)
         Filter: ((source)::text = ANY ('{source1,source2}'::text[]))
         Rows Removed by Filter: 1651946
         Buffers: shared hit=327620
 Planning time: 2.737 ms
 Execution time: 4117.573 ms
(11 rows)

postgresql sql postgresql-10
person Wandré Veloso    schedule 16.04.2018    source источник
comment
Ваше условие where выбирает около 1,8 млн строк из одной таблицы на основе второй таблицы. Я не знаю, какой производительности вы от него ждете - план идеально подходит для этих условий. Возможно, ваш work_mem слишком мал для этого, и БД должна использовать диск для временного файла - вы можете опубликовать explain (analyze, verbose, buffers) select ..., чтобы подтвердить это.   -  person Tometzky    schedule 17.04.2018

Ответы (1)


arrow_upward
0
arrow_downward

Для этого запроса:

SELECT thf.attr1, thf.attr2, thf.attr3, thf.attr4
FROM tb_one thf INNER JOIN
     tb_two ths
     ON ths.tb_hit_hitid = thf.tb_hit_hitid
WHERE ths.source IN ('source1', 'source2');

Вам нужны индексы для tb_two(source, tb_hit_hitid) и tb_one(tb_hit_hitid). Это, пожалуй, лучший показатель.

Если запрос возвращает дубликаты (из-за соединения), я мог бы предложить написать это как:

SELECT thf.attr1, thf.attr2, thf.attr3, thf.attr4
FROM tb_one thf
WHERE EXISTS (SELECT 1
              FROM tb_two ths
              WHERE ths.tb_hit_hitid = thf.tb_hit_hitid AND
                    ths.source IN ('source1', 'source2')
             );

Для этой версии вы хотите, чтобы индекс был tb_two(tb_hit_hitid, source).

person Gordon Linoff    schedule 16.04.2018
comment
В зависимости от распределения значений, возможно ли, что tb_two(tb_hit_hitid, source) будет лучше? - person Andrew Lazarus; 16.04.2018
comment
@Андрей Лазарус. . . Для первого запроса? Я сомневаюсь в этом. Вы, вероятно, захотите сначала отфильтровать предложение where. Я имею в виду, я полагаю, что возможно, что порядок индекса не имеет значения, но обычно фильтр на where будет первым. - person Gordon Linoff; 17.04.2018
comment
— я думал, что thf.source может иметь слишком мало различных значений, чтобы их можно было индексировать, в то время как соединение может быть очень избирательным. - person Andrew Lazarus; 17.04.2018