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