Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

Question

I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query:

SELECT * 
FROM category_member 
ORDER BY (p::float8/q) DESC 
LIMIT 10;

I am ordering by (p::float8/q) cause I am using a user specified order with fractions as index (I have the same table as in this link explained just with 10 million rows)

Limit  (cost=585557.40..585557.43 rows=10 width=24) (actual time=15865.330..15865.332 rows=10 loops=1)
  ->  Sort  (cost=585557.40..610557.40 rows=10000000 width=24) (actual time=15865.327..15865.327 rows=10 loops=1)
        Sort Key: (((p)::double precision / (q)::double precision))
        Sort Method: top-N heapsort  Memory: 25kB"
        ->  Seq Scan on category_member  (cost=0.00..369461.00 rows=10000000 width=24) (actual time=0.070..13528.741 rows=10000000 loops=1)
Planning time: 10.556 ms
Execution time: 15865.540 ms

This query takes to long in my eyes 16,135 s to get the first 10 rows of a table with 10 Million rows.

Anyone had the same issue and knows how to improve performance on my query?

CREATE UNIQUE INDEX ON category_member (category_id, (p::float8/q));

Answer 1

To improve the speed of that query, you need the following index:

create index on category_member ((p::float8/q));