sql - Improve this query

Question

do you know if there is a way to improve this query?

The database where this query runs is very large, and it could take a lot of time to return the result.

We have indexes on field_1, field_2 and state

We are using Postgres

SELECT id
FROM a
WHERE 
(field_1 = 999 OR field_2 = 876) AND state IN ('finished', 'closed')
ORDER BY created_at desc

One suggestion that I get is to split this query in two and then remove the "OR".

Another suggestion is to add some "pagination"

Answer 1

This is going to be quite hard to optimize as written. If you phrase this as:

SELECT id
FROM ((SELECT a.*
       FROM a
       WHERE field_1 = 999 AND state IN ('finished', 'closed')
      ) UNION ALL
      (SELECT a.*
       FROM a
       WHERE field_2 = 876 AND state IN ('finished', 'closed') and field_1 <> 999
      )
     ) a
ORDER BY created_at desc;

Then Postgres can use different indexes for each subquery:

• (field_1, state)
• (field_2, state, field_1)