Q:

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

A:

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).

