0

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
Improve this question
6
  • Do many questions in question_interaction have the is_seen flag set or not?
    – Vérace
    Aug 21 at 10:17
  • @Vérace It's a boolean field. Most of them won't have it set.
    – sujeet
    Aug 21 at 13:05
  • First thin I would do is to create a partial index on is_seen as follows: CREATE INDEX question_interaction_is_seen_pix ON question_interaction WHERE is_seen IS NOT NULL;. What is the difference between a student, a user and a customer - how many records per table (I assume they are also in separate tables) and how many times do they apppear in the question_interaction table (PK)? The complete DDL of question_interaction would be good - I'd work under the assumption that the _id fields are PKs of their respective tables!
    – Vérace
    Aug 21 at 17:28
  • The more information you give us, the more help we can provide!
    – Vérace
    Aug 21 at 17:30
  • 1
    The question should also show table definition (CREATE TABLE statement), Postgres version, and cardinalities: Roughly how many rows total, how many distinct combinations of (user_id, customer_id, student_id), how many distinct elements in your tags array, how many in the according filter (you show one) and how many is_seen IS NULL. Also, how much write load vs. read, and how important is this particular query?
    – Erwin Brandstetter
    Aug 23 at 2:06

1 Answer 1

Reset to default
0

The ideal index for this query would probably be:

create index on question_interaction (student_id, user_id, customer_id, question_id, "timestamp" DESC, is_seen, tags) WHERE is_seen IS NOT NULL

But where the first 3 columns can be in any order. It doesn't matter what order they are in, as all are tested for equality. That means their selectivities multiply together, and it doesn't matter much which order. After that, question_id, "timestamp" DESC matches the ORDER BY, which means tuples can be read from the index already in order, avoiding a sort operation. The last two columns are there only to support an index-only scan (and including "tags" might be a problem, as we don't know how large it might be). The WHERE clause on the index makes is_seen IS NOT NULL behave like an honorary first "column" in the index. You get to skip all the ones that are NULL, without having to do any work.

But maybe just those first 3 columns are selective enough so that it is good enough without any of the rest of the stuff I described being needed.

Improve this answer
2
  • Why are you suggesting to use Btree on tags If I am using tags @> ARRAY ['history']::TEXT[]?
    – sujeet
    Aug 23 at 5:59
  • As said, to support index-only scans. You could move it to an INCLUDE if you want. doing that might have some documentation value, but otherwise seems pointless.
    – jjanes
    Aug 23 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.