Внутреннее соединение для двух таблиц - с предикатами на большой таблице

Postgresql 9.3

У меня есть две таблицы data_prf с 200.000 и data_prf_norm с 22M записей.

SELECT n.k AS code, count(n.k) AS total 
FROM data_prf p 
INNER JOIN data_prf_norm n ON (p.data_prf_id = n.data_prf_id) 
WHERE (n.questionid = 5) AND (n.v = 1) 
GROUP BY n.k

Обс. Таблица data_prf_norm имеет 20M записей с v = 0 и 2M записей v = 1.

Это занимает около 3-5 секунд.

HashAggregate  (cost=378204.02..378204.08 rows=6 width=2)

->  Hash Join  (cost=100608.78..364361.49 rows=2768506 width=2)

    Hash Cond: (n.data_prf_id = p.data_prf_id)

    ->  Bitmap Heap Scan on data_prf_norm n  (cost=51824.36..230655.68 rows=2768506 width=6)

          Recheck Cond: (questionid = 5)

          ->  Bitmap Index Scan on data_prf_norm_questionid_v_idx  (cost=0.00..51132.23 rows=2768506 width=0)

                Index Cond: (questionid = 5)

    ->  Hash  (cost=44584.42..44584.42 rows=256000 width=4)

          ->  Index Only Scan using data_prf_pkey on data_prf p  (cost=0.42..44584.42 rows=256000 width=4)"

Есть ли способ сделать это быстро?

1) Есть только логическая целостность, поэтому в data_prf_norm нет нечетных записей

2) data_prf.data_prf_id да - это уникальный и первичный ключ.

3) да data_prf_norm.k НЕ НУЛЬ

4) Я создал все индексы

    CREATE INDEX data_prf_norm_data_prf_id_idx ON data_prf_norm USING btree (data_prf_id) 
    CREATE INDEX data_prf_norm_k_idx ON data_prf_norm USING btree (k);
    CREATE INDEX data_prf_norm_questionid_idx ON data_prf_norm USING btree (questionid);  
    CREATE INDEX data_prf_norm_v_idx ON data_prf_norm USING btree (v);

Это индексы, которые я использовал. Есть ли еще какой-нибудь недостающий индекс, чтобы ускорить работу? Я полагаю, 22M не такой уж большой, или я должен считать, что это большой стол?

Спасибо.


postgresql sql optimization indexing postgresql-performance
person Paul H.    schedule 02.11.2014    source источник
comment
Было бы полезно иметь определения таблиц с индексами. Однако, поскольку вы проходите через ~ 2768406 строк, это никогда не будет молниеносно. Вы не думали о денормализации?   -  person Wolph    schedule 03.11.2014
comment
Возможно ли, что data_prf p таблица избыточна в этом запросе? Из запроса и плана это выглядит так (но мы ничего не знаем о ваших данных)   -  person zerkms    schedule 03.11.2014
comment
Обратите внимание на информацию о теге, который я добавил: stackoverflow.com/tags/postgresql-performance/info. Самое главное, нам нужны определения таблиц со всеми соответствующими деталями и вашей версией Postgres. И некоторые пояснения к вашему запросу. Какие из условий стабильны? (Всегда n.v = 1?)   -  person Erwin Brandstetter    schedule 03.11.2014
comment
Нет, иногда n.v = 0, тогда я не буду использовать.   -  person Paul H.    schedule 21.04.2015

Ответы (1)


arrow_upward
2
arrow_downward

Предполагая недостающие детали:

  • ссылочная целостность (в data_prf отсутствует data_prf_norm.data_prf_id)
  • data_prf.data_prf_id уникален (ПК?). Итак, у нас есть соотношение 1: 1 или 1: n между data_prf и data_prf_norm, а не наоборот.
  • data_prf_norm.k определяется NOT NULL.

SELECT k AS code, count(*) AS total 
FROM   data_prf_norm
WHERE  questionid = 5
AND    v = 1
GROUP  BY k;

Основные моменты

  • Самое главное, что с гарантированной ссылочной целостностью вам вообще не нужно присоединяться к data_prf. Все предикаты находятся на data_prf_norm.

  • Если k определен NOT NULL, вы можете использовать более простой и немного более быстрый count(*).

  • Для лучшей производительности чтения создайте многоколоночный («покрывающий») индекс:

    CREATE INDEX ON data_prf_norm (v, question_id, k);

  • Далее, предполагая, что большинство или все запросы включают предикат v = 1, используйте вместо него частичный индекс:

    CREATE INDEX ON data_prf_norm (question_id, k) WHERE v = 1;
person Erwin Brandstetter    schedule 02.11.2014
comment
@PaulHodel: Вся эта информация (плюс отсутствующее определение таблицы) должна лучше уйти в вопрос. Воспользуйтесь функцией редактирования (слева под вашим вопросом). Комментарии с кодом читаются с трудом. Тогда оставьте короткий комментарий под моим ответом, чтобы привлечь мое внимание. - person Erwin Brandstetter; 18.11.2014