Flask-SqlAlchemy query with order_by and pagination is very slow

Ok, I found a way to solve that.

When i do exactly the same query but with SET enable_seqscan=off; it forces PostgreSQL to use Index Scan instead of Sequence Scan, and it gets much faster!

SET enable_seqscan=off;SELECT my_table.id, my_table.code, my_table.name FROM my_table ORDER BY my_table.code ASC LIMIT 10 OFFSET 1158960**EXPLAIN (ANALYZE):**"Limit  (cost=1814764.86..1814777.39 rows=8 width=131) (actual time=616.543..616.545 rows=8 loops=1)""  ->  Index Scan using ix_my_table_code on my_table  (cost=0.43..1814777.39 rows=1158968 width=131) (actual time=0.065..590.898 rows=1158968 loops=1)""Total runtime: 616.568 ms"

So now the point is - how do I set PostgreSQL config to make it use Index Scan without forcing it? I guess the answer is - "Planner Cost Constants". Any advices about them?

UPDATE 13.04.2016:

I finally figured out the situation and found a solution. In my case, everything is solved by setting Planner Cost Constants like this in postgresql.conf:

seq_page_cost = 1.0random_page_cost = 1.0cpu_tuple_cost = 0.01cpu_index_tuple_cost = 0.0001cpu_operator_cost = 0.0025effective_cache_size = 1024MB

Also, there're many advises to set effective_cache_size as 3/4 from a server entire RAM. Anyway, with these settings Planner always uses Index Scan on Huge tables. So timing is now 200-300ms.

Problem Solved.