CODEWITHSUNDEEP
sqlpostgresqlindexing
Macbernie
Macbernie

Reputation: 1323

Faster ORDER BY LIMIT OFFSET

I have a simple request like:

SELECT id, champs1, champs2, champs3, count(*) OVER() AS full_count
FROM my_table
ORDER BY champs1 ASC
LIMIT 10 OFFSET 0

LIMIT and OFFSET are used to paginate my results.

But the request is very slow: ~20 secondes. My "my_table" table has ~8 millions lines.

I put an index on the "champs1" column, but it still very slow.

How can I make this request faster ? Thanks for help

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

How long does the query take to run without the full_count? That may be part of the issue (I am not sure).

You might try this:

SELECT id, champs1, champs2, champs3, fc.full_count
FROM my_table CROSS JOIN
     (SELECT count(*) as full_count FROM my_table) fc
ORDER BY champs1 ASC
LIMIT 10 OFFSET 0;

Then, be sure you have an index on my_table(champs1).

Upvotes: 2

Related Questions