Проблемы производительности SQL с внутренним выбором в PostgreSQL для табличного отчета

Question

Использование базы данных PostgreSQL:

У меня есть приложение для опроса, в котором пользователи вводят действия и отвечают на вопросы о своих действиях. Сам опрос называетсяRECALLS_T, введенные события EVENTS_T, и ответы ANSWERS_T. Ответы на заданные вопросы о деятельности, хранящиеся вACTIVITY_QUESTIONS_T, сопоставленный поиском (LOOKUP_T).

Затем мне нужно запустить отчет на основе событий, в котором каждая строка является событием из EVENTS_Tза каждый отзыв (все события объединены для всех отзывов). Однако в некоторых столбцах этого отчета необходимо указать значение для определенных ответов, в противном случае эти ячейки имеют значение NULL. Итак, это табличный отчет.

Пример (сначала простой плоский материал, затем сложный табулированный материал):

RecallID | RecallDate | Event |..| WalkAlone | WalkWithPartner |..| ExerciseAtGym
256      | 10-01-19   | Exrcs |..| NULL      | NULL            |..| yes
256      | 10-01-19   | Walk  |..| yes       | NULL            |..| NULL
256      | 10-01-19   | Eat   |..| NULL      | NULL            |..| NULL
257      | 10-01-19   | Exrcs |..| NULL      | NULL            |..| yes

Мой SQL имеет внутренние выборки для табулированных столбцов на основе ответов и выглядит так:

select 
-- Easy flat stuff first
r.id as recallid, r.recall_date as recalldate, ... ,

-- Example of Tabulated Columns:
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l 
where l.id=aq.answer_choice_id and aq.question_id=13 
and aq.id=ans.activity_question_id and aq.activity_id=27 and ans.event_id=e.id) 
     as transportationotherintensity,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=66 and l.id=aq.answer_choice_id and aq.question_id=14
and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as commutework,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=67 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as commuteschool,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=95 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as dropoffpickup,

SQL работает, и отчет отображается, но производительность плохая. Я убедился, что это пропорционально плохо: нет волшебной пули для конкретного элемента, которая могла бы это исправить. Каждый внутренний выбор способствует плохой работе. Набор результатов из 1000 строк занимает 15 секунд, но не более 2–3 секунд.

Обратите внимание, что эти индексы уже существуют:

• ANSWERS_T: на ACTIVITY_QUESTION_ID, EVENT_ID
• EVENTS_T: на RECALL_ID
• ACTIVITY_QUESTIONS_T: на ACTIVITY_ID, QUESTION_ID, ANSWER_CHOICE_ID

Что-то я делаю не так с этими внутренними выборами?

Answer 1

Чтобы обобщить вопросы, вы хотите использовать условное агрегирование. В PostgreSQL вы можете использовать:

select ans.event_id,
       max(l.description) filter (where aq.question_id = 13 and aq.activity_id = 27) as transportationotherintensity
       max(l.description) filter (where l.id = 66 and aq.question_id = 14 and aq.activity_id = 67) as commutework,
       . . .
from activity_questions_t aq join
     lookup_t l
     on l.id = aq.answer_choice_id join
     answers_t ans
     on aq.id = ans.activity_question_id
group by ans.event_id