Optimizing PostgreSQL Query Using Composite Index and GIN Index

Question

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

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.

Answer 1

Using the Bitmap would mean it can no longer rely on the rows being retrieved already ordered by (question_id, timestamp DESC). Apparently the planner believes that the ordering without a sort is more important than the extra filtering.

But in this case I don't think that that is it. It thinks it will only find one row from the subquery, and needing to sort one rows is not something worth avoiding. We don't know how many rows it things it will filter out, that is not something the plan discloses. If you rerun the query without the tags condition, we could see what it is thinking there. But if we assume the ratio of rows actually filtered out to actually returned (26180/6466=4) is about correct to what it was thinking (not a very reliable assumption), it would be thinking there were to be 4 rows needing to be removed, either by the filter or by the bitmap. To use the BitmapAnd, it would have to start by building a large bitmap, because 'history' is not all that selective. That would probably not be seen as worthwhile.