Postgres reduce by key if key is not null
Assuming you want what your current query does (which seems different from what your description says).

You ask:


  I think that problem is with filter but how to make it use index?


Your query plan shows that Postgres is already using indexes for every step of the way. The added FILTER step only filters rows according to your additional predicates.

I posted a different idea at first that was missing ORDER BY seq. This query using a simple NOT EXISTS anti-semi-join should be a better equivalent:

SELECT user_id
     , seq
     , timestamp
     , reduce_key
     , mapping
FROM   user_sequence u
WHERE  user_id = 860862404
AND    seq &gt; 3562974
AND    NOT EXISTS (
   SELECT 1
   FROM   user_sequence
   WHERE  user_id = u.user_id
   AND    reduce_key = u.reduce_key
   AND    seq &gt; u.seq
   )
ORDER  BY seq
LIMIT  1000;


Your existing indexes look good for it. The second one might be a bit more efficient with descending sort order for the last item:

user_sequence_user_id_reduce_key_seq_idx btree (user_id, reduce_key, seq DESC)

Normally, there would be a simpler alternative with DISTINCT ON, but that would fold all rows with reduce_key IS NULL into a single row while you want them all.

Related answer with more explanation:


How do I efficiently get &quot;the most recent corresponding row&quot;?


Aside: You have a lot of unnecessary parentheses and double-quotes. Maybe an inefficient ORM?