0

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?

8
  • It's due to the probability, it's over 90% chance that I will find the row in the most recent table, and over 99% that I will find it in one of the two most recent. In actual scenario, I will have 6 partitions and I don't want to waste time by scanning the first 4 partitions every time. Jun 30, 2022 at 11:50
  • I am not looking to limit the scan using this, I was already able to try 'limit 1' and it did stop looking after it found a row. My question is only regarding the order in which the scan is done. Is there a way to reverse that? Jun 30, 2022 at 11:54
  • Yeah, I didn't include that here as I got that working. My question is just regarding the order. Even if it scans all partitions, it is fine for me as long as I can change the order. Jun 30, 2022 at 11:58
  • I am also afraid that there isn't since searching around didn't lead to anything. Hoping someone can chime in with some workaround. Jun 30, 2022 at 11:58
  • Thanks, will take care of that! Jun 30, 2022 at 12:01

1 Answer 1

3

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.

3
  • Hey, this is good, but is there a way to do this without indexing the date column? I will be indexing the value column on which I will actually be doing the search. Jul 1, 2022 at 11:07
  • Create multicolumn index on (value, date). Planner needs presorted data to doing such optimisation.
    – Melkij
    Jul 1, 2022 at 11:28
  • Ah, okay. I don't have any other use for that index, will you say the added bloat is worth it? Alternatively, I am just considering implementing this in my application. Jul 1, 2022 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.