I'm working on optimizing a query for a PostgreSQL database used in an educational platform.
The query retrieves distinct
question_id values along with their
is_seen status from the
question_interaction table based on specific
tag criteria. Here's the query:
SELECT DISTINCT ON (question_id) question_id, is_seen FROM question_interaction WHERE user_id = 'USR234342' AND customer_id = '39874513-8bb1-461e-a73f-9e73fa31870d' AND student_id = '8179483214' AND tags @> ARRAY ['history']::TEXT AND is_seen IS NOT NULL ORDER BY question_id, timestamp DESC;
Here is the selectivity order of the columns:
question_id > student_id > user_id > customer_id
Can someone provide guidance on how to create an effective index for this query?
Specifically, I'm looking for recommendations on:
- The choice of columns for the index
- The order of columns in the index
- Any additional conditions to consider