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 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 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.