Запрос со многими самостоятельными соединениями выполняется медленно

У меня есть запрос, который включает в себя множество соединений с одной и той же таблицей, в которой хранятся данные о лидах. В кампаниях, в которых у меня много потенциальных клиентов (5 тыс.), запросы выполняются очень медленно. К вашему сведению, в таблице есть 40657335 записей, которые имеют множество соединений с собой. У меня настроены индексы. У кого-нибудь есть предложения, как это исправить?

SELECT to_char(DATE, 'MM/DD/YYYY HH:MI:SS AM') AS "Date"
    ,email AS "Email"
    ,f512.value AS "f512value"
    ,f341.value AS "f341value"
    ,f11.value AS "f11value"
    ,f12.value AS "f12value"
    ,f15.value AS "f15value"
    ,f14.value AS "f14value"
    ,UPPER(f21.value) AS "f21value"
    ,f20.value AS "f20value"
    ,f19.value AS "f19value"
    ,f22.value AS "f22value"
    ,f225.value AS "f225value"
    ,f223.value AS "f223value"
    ,f24.value AS "f24value"
    ,f28.value AS "f28value"
    ,f228.value AS "f228value"
    ,f229.value AS "f229value"
    ,f231.value AS "f231value"
    ,f230.value AS "f230value"
    ,f232.value AS "f232value"
    ,f233.value AS "f233value"
    ,f234.value AS "f234value"
    ,f235.value AS "f235value"
    ,f264.value AS "f264value"
    ,f246.value AS "f246value"
    ,f247.value AS "f247value"
    ,f248.value AS "f248value"
    ,f249.value AS "f249value"
    ,f250.value AS "f250value"
    ,f251.value AS "f251value"
    ,f252.value AS "f252value"
    ,f253.value AS "f253value"
    ,f314.value AS "f314value"
    ,f254.value AS "f254value"
    ,f255.value AS "f255value"
    ,f256.value AS "f256value"
    ,f236.value AS "f236value"
    ,f242.value AS "f242value"
    ,f241.value AS "f241value"
    ,f257.value AS "f257value"
    ,f258.value AS "f258value"
    ,f259.value AS "f259value"
    ,f260.value AS "f260value"
    ,f261.value AS "f261value"
    ,f262.value AS "f262value"
    ,f263.value AS "f263value"
    ,f243.value AS "f243value"
    ,f244.value AS "f244value"
    ,f237.value AS "f237value"
    ,f238.value AS "f238value"
    ,f239.value AS "f239value"
    ,f240.value AS "f240value"
    ,f245.value AS "f245value"
    ,f82.value AS "f82value"
    ,f127.value AS "f127value"
    ,classification AS "Classification"
    ,sent_to AS "Leads Sent To"
    ,delivery_method AS "Delivery Method"
    ,sent_date AS "Date Lead Sent"
    ,source AS "Source"
FROM reg_event_uniques
LEFT JOIN reg_event_fields f512 ON f512.field_id = 512
    AND f512.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f341 ON f341.field_id = 341
    AND f341.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f11 ON f11.field_id = 11
    AND f11.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f12 ON f12.field_id = 12
    AND f12.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f15 ON f15.field_id = 15
    AND f15.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f14 ON f14.field_id = 14
    AND f14.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f21 ON f21.field_id = 21
    AND f21.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f20 ON f20.field_id = 20
    AND f20.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f19 ON f19.field_id = 19
    AND f19.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f22 ON f22.field_id = 22
    AND f22.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f225 ON f225.field_id = 225
    AND f225.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f223 ON f223.field_id = 223
    AND f223.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f24 ON f24.field_id = 24
    AND f24.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f28 ON f28.field_id = 28
    AND f28.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f228 ON f228.field_id = 228
    AND f228.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f229 ON f229.field_id = 229
    AND f229.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f231 ON f231.field_id = 231
    AND f231.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f230 ON f230.field_id = 230
    AND f230.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f232 ON f232.field_id = 232
    AND f232.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f233 ON f233.field_id = 233
    AND f233.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f234 ON f234.field_id = 234
    AND f234.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f235 ON f235.field_id = 235
    AND f235.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f264 ON f264.field_id = 264
    AND f264.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f246 ON f246.field_id = 246
    AND f246.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f247 ON f247.field_id = 247
    AND f247.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f248 ON f248.field_id = 248
    AND f248.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f249 ON f249.field_id = 249
    AND f249.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f250 ON f250.field_id = 250
    AND f250.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f251 ON f251.field_id = 251
    AND f251.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f252 ON f252.field_id = 252
    AND f252.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f253 ON f253.field_id = 253
    AND f253.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f314 ON f314.field_id = 314
    AND f314.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f254 ON f254.field_id = 254
    AND f254.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f255 ON f255.field_id = 255
    AND f255.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f256 ON f256.field_id = 256
    AND f256.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f236 ON f236.field_id = 236
    AND f236.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f242 ON f242.field_id = 242
    AND f242.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f241 ON f241.field_id = 241
    AND f241.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f257 ON f257.field_id = 257
    AND f257.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f258 ON f258.field_id = 258
    AND f258.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f259 ON f259.field_id = 259
    AND f259.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f260 ON f260.field_id = 260
    AND f260.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f261 ON f261.field_id = 261
    AND f261.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f262 ON f262.field_id = 262
    AND f262.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f263 ON f263.field_id = 263
    AND f263.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f243 ON f243.field_id = 243
    AND f243.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f244 ON f244.field_id = 244
    AND f244.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f237 ON f237.field_id = 237
    AND f237.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f238 ON f238.field_id = 238
    AND f238.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f239 ON f239.field_id = 239
    AND f239.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f240 ON f240.field_id = 240
    AND f240.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f245 ON f245.field_id = 245
    AND f245.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f82 ON f82.field_id = 82
    AND f82.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f127 ON f127.field_id = 127
    AND f127.reg_id = reg_event_uniques.reg_event_id
WHERE reg_event_uniques.cid = 3741
    AND (
        UPPER(f21.value) = 'UNITED STATES'
        OR UPPER(f21.value) = 'CANADA'
        OR UPPER(f21.value) != 'UNITED STATES'
        AND UPPER(f21.value) != 'CANADA'
        AND f21.value IS NOT NULL
        OR f21.value IS NULL
        )
    AND (
        reg_event_uniques.classification = 'End user or machine builder'
        OR reg_event_uniques.classification = 'Interested in sponsoring'
        OR reg_event_uniques.classification = 'Automation suppliers'
        )
ORDER BY DATE DESC LIMIT 100;

Вот так выглядит моя таблица:

id | field_id    | value                           | reg_id
---------------------------------------------------------------
 1 | 11          | first name value would go here  |   2234
 2 | 12          | last name value would go here   |   2234
 3 | 13          | middle name vlaue would go here |   2234
 4 | 14          | address 1 value would go here   |   2234
 5 | 15          | address 2 value would go here   |   2234
...
...

postgresql sql database-design entity-attribute-value postgresql-performance
person albertski    schedule 16.04.2015    source источник
comment
так каков результат EXPLAIN и EXPLAIN ANALYZE? напишите еще в вопросе..   -  person Kokizzu    schedule 16.04.2015
comment
похоже, что модель EAV пошла не так   -  person cha    schedule 16.04.2015
comment
Какие именно строки должно отфильтровывать составное условие WHERE для f21.value? Мне это кажется способом Руба Голдберга сказать И 1.   -  person John Bollinger    schedule 16.04.2015
comment
В чем причина такого большого количества самообъединений? Вы хотите, чтобы отдельные значения идентификатора были столбцами? Если это так, рассмотрите возможность запроса перекрестной таблицы, в котором вы сводите/переставляете данные от field id?   -  person Parfait    schedule 16.04.2015
comment
Является ли (reg_id, field_id) уникальным в reg_event_fields? Если нет, то у вас серьезные проблемы. Если да, есть ли у вас ограничение, обеспечивающее его соблюдение?   -  person John Bollinger    schedule 16.04.2015
comment
@JohnBollinger f21.value — это поле страны.   -  person albertski    schedule 16.04.2015
comment
@Parfait В этой таблице хранятся данные о лидах для кампании, а кампания должна быть динамичной, поэтому в кампании 1 может быть 20 полей (имя, фамилия, адрес и т. д.), а в кампании 2 может быть только 2 поля. Я обновил вопрос, чтобы дать вам представление о том, как выглядит моя таблица.   -  person albertski    schedule 16.04.2015
comment
@albertski, да, я это вижу. Я говорю, что ваше предложение where имеет составное условие для f21.value, которое кажется бесполезным из-за разрешения всех возможных значений.   -  person John Bollinger    schedule 16.04.2015
comment
@JohnBollinger field_id — это внешний ключ к моей таблице полей. Например, 11 в моей таблице полей будет именем. reg_id также является внешним ключом к другой таблице.   -  person albertski    schedule 16.04.2015
comment
@JohnBollinger Вы правы, я думаю, что здесь есть проблема. Спасибо. Я все еще не думаю, что это то, что вызывает медлительность.   -  person albertski    schedule 16.04.2015
comment
@albertski, это был не мой вопрос. Я спросил, должна ли комбинация field_id и reg_id быть уникальной в таблице reg_event_fields. То, что каждый из них является внешним ключом, ссылающимся на другую таблицу, не имеет отношения к вопросу.   -  person John Bollinger    schedule 16.04.2015
comment
У вас есть куча столбцов, которые противоречат продемонстрированному макету таблицы. classification, sent_to, delivery_method и т. д. Вы также забыли квалификацию таблицы, ваш запрос даже не может работать. И твой вопрос сломан...   -  person Erwin Brandstetter    schedule 16.04.2015
comment
@JohnBollinger Ой, извините! Да, комбинация field_id и reg_id уникальна.   -  person albertski    schedule 16.04.2015

Ответы (2)


arrow_upward
3
arrow_downward

Правильная схема базы данных

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

У вас есть более 50 объединений в таблице с 40 миллионами строк. Конечно, это будет медленно! Цитируя @cha, похоже, что модель EAV пошла очень неправильно. Прочитайте это:

Решение состоит в том, чтобы реконструировать вашу базу данных. Либо используйте тип документа, такой как hstore или jsonb, либо используйте широкую строку со многими столбцами, допускающими значение NULL.

20 полей, как вы прокомментировали (или 100 полей), которые могут быть NULL, стоят почти ничего. Хранилище NULL очень дешево. 64 столбца со значением NULL занимают только 8 байт в растровом изображении NULL. В отличие от вашей модели, которая очень дорогая. Накладные расходы для одной дополнительной строки составляют не менее 28 байт:

Пока застрял с этим запросом

Все ваши условия на f21.value сгорают до .. ничего. Поле должно быть либо USA, либо CAN, либо нет (USA или CAN), либо NULL - короче: это может быть все что угодно, это просто шум. Убери это.

Если бы условия для f21.value имели смысл, вы бы использовали JOIN вместо LEFT JOIN и переместили соединение на f21 вверх. При таком количестве объединений Postgres больше не может правильно оптимизировать запрос. Вы должны убедиться, что соединения уже написаны в полезном порядке. Даже не пытайтесь оптимизировать, это будет пустой тратой времени. Выполнить с set join_collapse_limit = 1:

SET LOCAL join_collapse_limit = 1;

SELECT to_char(DATE, 'MM/DD/YYYY HH:MI:SS AM') AS "Date"
     , ...
FROM   reg_event_uniques r
JOIN  reg_event_fields f21 ON f21.field_id = 21
    AND f21.reg_id = reg_event_uniques.reg_event_id
LEFT JOIN reg_event_fields f512 ON f512.field_id = 512
    AND f512.reg_id = reg_event_uniques.reg_event_id
...
LEFT JOIN reg_event_fields f127 ON f127.field_id = 127
    AND f127.reg_id = reg_event_uniques.reg_event_id
WHERE  r.cid = 3741
AND    r.classification IN
        ('End user or machine builder'
       , 'Interested in sponsoring'
       , 'Automation suppliers')
AND    f21.value ...  -- some useful condition
ORDER  BY DATE DESC
LIMIT  100;
person Erwin Brandstetter    schedule 16.04.2015

arrow_upward
1
arrow_downward

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

Предположим, что данная регистрация имеет не более одного значения для каждого поля, по крайней мере множественных совпадений не произойдет. Вы также можете переписать запрос без большого количества объединений — фактически только с одним. Что-то вроде этого:

SELECT *
FROM (
    SELECT
      reg_event_id
      ,to_char(DATE, 'MM/DD/YYYY HH:MI:SS AM') AS "Date"
      ,email AS "Email"
      ,classification AS "Classification"
      ,sent_to AS "Leads Sent To"
      ,delivery_method AS "Delivery Method"
      ,sent_date AS "Date Lead Sent"
      ,source AS "Source"
    FROM reg_event_uniques
    WHERE cid = 3741
      AND CASE classification
          WHEN 'End user or machine builder' THEN 1
          WHEN 'Interested in sponsoring' THEN 1
          WHEN 'Automation suppliers' THEN 1
          ELSE 0
          END
    ORDER BY reg_event_uniques.date DESC
    LIMIT 100
  ) uniq
  LEFT JOIN (
    SELECT 
      reg_id
      ,MAX(CASE field_id WHEN 1 THEN value END) AS "f1value"
      ,MAX(CASE field_id WHEN 2 THEN value END) AS "f2value"
      ,MAX(CASE field_id WHEN 3 THEN value END) AS "f3value"
      -- ...
    GROUP BY reg_id
  ) fields
    ON fields.reg_id = uniq.reg_event_id

Поскольку ваши условия фильтрации для всего запроса основывались только на столбцах из таблицы reg_event_uniques, и поскольку эта таблица является самой внешней таблицей в исходном запросе, эта версия использует встроенное представление для выбора только тех строк reg_event_uniques, которые влияют на окончательный результат. Обратите внимание, что он упорядочивает по столбцу date базовой таблицы, а не по столбцу date результатов, так как последний даст неправильный порядок (лексикографический). Это встроенное представление может быть полезным, а может и не быть, поскольку планировщик запросов может выполнить ту же самую реорганизацию исходного запроса, если он достаточно умен.

Основное достижение заключается в том, чтобы свести таблицу полей к набору строк, имеющему ровно одну строку на reg_id и содержащему все соответствующие значения полей в виде отдельных столбцов. Для этой цели используется встроенное представление с агрегированным запросом. Использование здесь функции MAX() не имеет особого значения, так как для каждого столбца она будет определять максимум не более одного значения, отличного от NULL. Точно с таким же эффектом можно было бы использовать функцию MIN().

person John Bollinger    schedule 16.04.2015