What is the best way to optimize sql query that hits/reads too many shared blocks?

I have the following table in my postgres db (version 11):

CREATE TABLE instance  (    id           bigserial,    period       DATERANGE   NOT NULL,    status       TEXT        NOT NULL,  -- (wait, active, inactive, outdated)    position     BIGINT      NOT NULL,      CONSTRAINT instance_pk PRIMARY KEY (id),    CONSTRAINT instance_period_check CHECK (NOT isempty(period))  );  

I need to change status of instances ordered by position by batches size of 1000 from java code:

        List<Instance> instances;          Long position = null;          do {              instances = dao.getInstancesBeforePeriod(fromStatus, position);              if (instances.isEmpty()) {                  break;              }              processBatch(toStatus, instances);              position = instances.get(instances.size() - 1).getPosition();          } while (true);  

dao.getInstancesBeforePeriod(fromStatus, position) if position == null calls the query:

SELECT id, status, period, position        FROM instance               WHERE status = :status                  AND upper(period) < now()              ORDER BY position              LIMIT 1000;  

if position != null calls the query:

SELECT id, status, period, position        FROM instance               WHERE status = :status                  AND upper(period) < now()                  AND position > :position              ORDER BY position              LIMIT 1000;  

But the first query hits/reads too many shared blocks so the query fails with timeout exception. How can I solve the problem?

What if I'll add an index on instance table:

create index concurrently instance_index_status_upper_period_position  on instance(status, upper(period), position)  

But I want to the index to keep instances ordered by position. Is it possible? Should I change the first query by adding position > 0 to where clause to use such kind of index?

The explain analyze result for the first query:

I'll apreciate any ideas. Thanks! :)

https://stackoverflow.com/questions/65747200/what-is-the-best-way-to-optimize-sql-query-that-hits-reads-too-many-shared-block January 16, 2021 at 02:54PM