Partition scan order in Postgres

Question

I have created a natively partitioned table in Postgres and when trying to query this, Postgres is scanning the partitions in ascending order. The partition key is a timestamp column. For my use case, the application has more probability of finding the row in the recent tables, thus I want Postgres to scan the partitions in descending order, but I am not able to find a way to make this happen.

Sample table that I have tried this on:

create table test (id varchar(255), value varchar(255), date timestamp with time zone) partition by range(date);

create table test_202205 partition of test for values from ('2022-05-01') to ('2022-06-01');

create table test_202206 partition of test for values from ('2022-06-01') to ('2022-07-01');

create table test_202207 partition of test for values from ('2022-07-01') to ('2022-08-01');

insert into test values('aa','foo',now()); //Goes to June table

explain analyse select * from test where value = 'foo' order by date desc;


                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Sort  (cost=32.66..32.67 rows=3 width=1040) (actual time=0.024..0.025 rows=0 loops=1)
   Sort Key: test_202205.date DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Append  (cost=0.00..32.64 rows=3 width=1040) (actual time=0.010..0.011 rows=0 loops=1)
         ->  Seq Scan on test_202205  (cost=0.00..10.88 rows=1 width=1040) (actual time=0.004..0.004 rows=0 loops=1)
               Filter: ((value)::text = 'foo'::text)
         ->  Seq Scan on test_202206  (cost=0.00..10.88 rows=1 width=1040) (actual time=0.005..0.005 rows=0 loops=1)
               Filter: ((value)::text = 'foo'::text)
         ->  Seq Scan on test_202207  (cost=0.00..10.88 rows=1 width=1040) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: ((value)::text = 'foo'::text)
 Planning Time: 0.099 ms
 Execution Time: 0.052 ms
(12 rows)

Even when trying to order the partition key in descending order, it still scans the partitions in ascending order, while I would like to scan in descending order. Is there a way to make this happen?

Answer 1

Postgresql 12 uses ordered partition scan automatically when possible.

Let's write some data, index on date and check behaviour:

insert into test select 'id'||i, 'v', date '2022-08-01' - interval '10 min' * i from generate_series(1, 10000) as i;
INSERT 0 10000
create index on test (date);
CREATE INDEX
explain analyse select * from test where value = 'v' order by date desc limit 1;
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.84..0.89 rows=1 width=16) (actual time=0.030..0.031 rows=1 loops=1)
   ->  Append  (cost=0.84..451.86 rows=10000 width=16) (actual time=0.029..0.029 rows=1 loops=1)
         ->  Index Scan Backward using test_202207_date_idx on test_202207 test_3  (cost=0.28..173.40 rows=4464 width=16) (actual time=0.028..0.028 rows=1 loops=1)
               Filter: ((value)::text = 'v'::text)
         ->  Index Scan Backward using test_202206_date_idx on test_202206 test_2  (cost=0.28..166.90 rows=4320 width=16) (never executed)
               Filter: ((value)::text = 'v'::text)
         ->  Index Scan Backward using test_202205_date_idx on test_202205 test_1  (cost=0.28..61.56 rows=1216 width=17) (never executed)
               Filter: ((value)::text = 'v'::text)
 Planning Time: 0.556 ms
 Execution Time: 0.052 ms

As you can see, only one partition was actually scanned.