I have a PostgreSQL query that involves multiple conditions and joins. While I've created a composite index to speed up some of the filtering, I'm still experiencing extra rows being fetched due to the tags filter. Additionally, PostgreSQL doesn't seem to be effectively combining my composite index with the
GIN index on tags.
SELECT DISTINCT ON (question_id) question_id, is_seen
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
AND score IS NOT NULL
ORDER BY question_id, timestamp DESC;
Here is the explain https://explain.depesz.com/s/3Ks9e
I've created a composite index on
(student_id, user_id, customer_id, question_id, timestamp desc), which improves filtering on the first three conditions. Additionally, I've added a
GIN index on the
Explain shows, the index
(student_id, user_id, customer_id, question_id, timestamp desc) is being used and after fetching the rows it applies the
tag filter, but the query is a bit faster now.
I was thinking it will use a bitmap scan using both the indexes, but it is not using them and it is still using the same index.