Postgresql offset optimization in partitions

Question

I created a table:

create table test (id serial, name varchar(20), rownumber serial) partition by range (id);

then I created index

create index "test_rownumber_id_index" on test using btree (id,rownumber);

then I created partitions:

create table test_1_5 partition of test for values from (1) to (5);
create table test_5_10 partition of test for values from (5) to (10);

then i inserted 9 rows. Now, I want to emulate query with offset

select id from test where rownumber>5 limit 10;

In

explain select id from supertest2 where rowsnumber>5 limit 10;

I get

Limit  (cost=0.00..0.77 rows=10 width=4)
  ->  Append  (cost=0.00..44.37 rows=574 width=4)
        ->  Seq Scan on test_1_5 test  (cost=0.00..20.75 rows=287 width=4)
              Filter: (rownumber > 5)
        ->  Seq Scan on test_5_10 test  (cost=0.00..20.75 rows=287 width=4)
              Filter: (rownumber > 5)

The question is how Postgres do this query? Does postgres go through partition test_1_5 linearly, or just find in btree index the rownumber with value > 5 and after he find this node get limit linearly?

I need Postgres to ignore partition test_1_5 in queries like this.

Answer 1

when you partition by specific value then you have to use partition key in WHERE condition

like :

select id from test where id>5 limit 10;