CODEWITHSUNDEEP
sqlpostgresql
user15178581
user15178581

Reputation: 69

Is there an index that can make the following query faster

I need to make the following query faster:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id ASC) AS rownum
      FROM table
      WHERE user = $1 AND bool_value = False) x
WHERE rownum = $3

Is there an index that can do this?

Upvotes: 1

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

I would suggest writing this as:

SELECT *
FROM table
WHERE user = $1 AND bool_value = False
ORDER BY id
OFFSET $3 FETCH FIRST 1 ROW ONLY;

Note that $3 here is one less than in your version. And, this doesn't include the row number in the result set -- although you could easily add that if you really do want it.

Then you want an index on (user, bool_value, id). The first two columns can be in either order. This index should also work on your query.

Upvotes: 3

Related Questions