Necessary to include filtered index filter column if value always NULL in PostgreSQL?

1 vote

0 answers

31 views

postgresql index postgresql-performance filtered-index isnull

                          My question is about PostgreSQL. I found similar questions for MS SQL server but I don't know if the answers apply here. 

My table looks like this:

    scores
    ======
    | ID | UserID | ValidFrom  | ValidUntil | ScorePoints |
    +----+--------+------------+------------+-------------|
    |  1 |      1 | 2025-08-01 | 2025-08-02 |          80 |
    |  2 |      1 | 2025-08-02 |       NULL |          85 |

There will be a lot of queries to find the currently valid score for a given UserID (= the row for UserID = ? AND ValidUntil IS NULL).

I have a unique index like this:

    CREATE UNIQUE INDEX ix_uq_scores ON scores ( userid ) WHERE validuntil IS NULL;

Or should it be:

    CREATE UNIQUE INDEX ix_uq_scores ON scores ( userid, validuntil ) WHERE validuntil IS NULL;

A query might look like

    SELECT u.id, u.username, s.scorepoints 
    FROM users u 
    INNER JOIN scores s ON s.userid = u.id AND s.validuntil IS NULL
    WHERE u.id = 123;

My filtered index will only include rows where validuntil **IS** NULL. So do I have to add this constant NULL value to the index tuple?
                        

Asked by MrSnrub (181 rep)

Aug 4, 2025, 02:32 AM

Necessary to include filtered index filter column if value always NULL in PostgreSQL?

Related Questions