I have a table with those columns: id, uuid, customer_id, building, dept, values

I have a combined index on those columns: building, dept.

The table contains ~80 millions of rows

I would like to apply a pagination on this basic query: select * from my_table. So I have to introduce a unique column because that is the only way the order by correctness is guaranteed: select * from my_table order by id asc offset 0 limit 1000. I used limit and offset even if there are other techniques like > id, ubt I think for now it is not important.

My problem is that now I have to introduce filters to this pagination, as it is expanding as a search feature. Then I have to filter by building and department. Here the new query: select * from my_table where building = 666 and dept = 'my_dept' order by id asc offset 0 limit 1000.

Now in order to maintain the order by correctness I have to maintain the order by id clause but it is going to slow down the query (10+ minutes) as the id is not in the combined index.

The above table is just a small representation of a real table where it contains more fields , like 6 not so bad, but in order to maintain the order by id every index is not valid anymore or I should include the id field everywhere.

am I approaching in the wrong way?
Are here any thoughts/suggestions?

Thanks in advance

  • 1
    You won't be able to efficiently search for arbitrary criteria. Limit yourself to one or two and index those well. May 27, 2022 at 17:50
  • By the time you are up to 6 fields, the number of rows left should be small enough that using a sort to do the ordering should be very fast. If not, then add 'id' to that index as well
    – jjanes
    May 28, 2022 at 12:56


Your Answer

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