Sample Header Ad - 728x90

Optimizing PostgreSQL Query with DISTINCT ON, Filtering, and Ordering

0 votes
1 answer
172 views
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 user, customer_id, student_id, and 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
Asked by sujeet (257 rep)
Aug 21, 2023, 07:06 AM
Last activity: Jul 13, 2025, 09:04 PM