idx
digoal=# explain verbose select * from aa order by a limit 10 offset 90; QUERY PLAN ------------------------------------------------------------------------------------- Limit (cost=3.64..4.01 rows=10 width=22) Output: a, b -> Index Scan using aa_pkey on public.aa (cost=0.28..112.30 rows=3001 width=22) Output: a, b (4 rows) digoal=# explain verbose select * from aa where a<2000 order by a limit 10 offset 90; QUERY PLAN ------------------------------------------------------------------------------------ Limit (cost=4.15..4.58 rows=10 width=22) Output: a, b -> Index Scan using aa_pkey on public.aa (cost=0.28..44.13 rows=1020 width=22) Output: a, b Index Cond: (aa.a < 2000) (5 rows)
digoal=# truncate table aa; TRUNCATE TABLE
digoal=# create index idx_b on aa(b); CREATE INDEX
digoal=# insert into aa select generate_series(1,1000),random(); INSERT 0 1000
digoal=# explain analyze select * from aa order by a limit 10 offset 90; QUERY PLAN ------------------------------------------------------------------------------------------------------ Limit (cost=3.64..4.01 rows=10 width=22) (actual time=0.083..0.089 rows=10 loops=1) -> Index Scan using aa_pkey on aa (cost=0.28..112.30 rows=3001 width=22) (actual time=0.034..0.076 rows=100 loops=1) Total runtime: 0.116 ms (3 rows)