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
Last activity: Jul 13, 2025, 09:04 PM