we use Postgres 14 and we are trying to optimize a query to a partitioned table, the query looks something like this

SELECT col1, col2 FROM partitioned_table WHERE profile_id = '00000000-0000-0000-0000-000000000000' AND product_id = 'product_a' ORDER BY created_at DESC LIMIT 500;

This is the query plan

                                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------
 Limit  (cost=944.59..945.84 rows=500 width=202) (actual time=39.501..39.691 rows=500 loops=1)
   ->  Sort  (cost=944.59..947.09 rows=1000 width=202) (actual time=39.499..39.660 rows=500 loops=1)
         Sort Key: partitioned_table.created_at DESC
         Sort Method: top-N heapsort  Memory: 290kB
         ->  Append  (cost=0.71..894.76 rows=1000 width=202) (actual time=0.030..27.204 rows=32867 loops=1)
               ->  Index Scan using partitioned_table_profile_id_product_id_trade_id_idx on partitioned_table_legacy partitioned_table_1  (cost=0.71..772.99 rows=379 width=116) (actual time=0.029..22.550 rows=32838 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240601_profile_id_product_id_trade_id_created_idx on partition_20240601 partitioned_table_2  (cost=0.56..12.65 rows=5 width=117) (actual time=0.019..0.019 rows=0 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240602_profile_id_product_id_trade_id_created_idx on partition_20240602 partitioned_table_3  (cost=0.56..12.65 rows=5 width=117) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240603_profile_id_product_id_trade_id_created_idx on partition_20240603 partitioned_table_4  (cost=0.56..18.68 rows=8 width=117) (actual time=0.014..0.017 rows=3 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240604_profile_id_product_id_trade_id_created_idx on partition_20240604 partitioned_table_5  (cost=0.56..4.58 rows=1 width=117) (actual time=0.013..0.014 rows=2 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240605_profile_id_product_id_trade_id_created_idx on partition_20240605 partitioned_table_6  (cost=0.56..16.66 rows=7 width=117) (actual time=0.020..0.021 rows=2 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240606_profile_id_product_id_trade_id_created_idx on partition_20240606 partitioned_table_7  (cost=0.56..14.67 rows=6 width=117) (actual time=0.013..0.014 rows=1 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Index Scan using partition_20240607_profile_id_product_id_trade_id_created_idx on partition_20240607 partitioned_table_8  (cost=0.56..36.90 rows=17 width=117) (actual time=0.015..0.037 rows=21 loops=1)
                     Index Cond: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Seq Scan on partition_20240608 partitioned_table_9  (cost=0.00..0.00 rows=1 width=265) (actual time=0.014..0.015 rows=0 loops=1)
                     Filter: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
               ->  Seq Scan on partition_20240609 partitioned_table_10  (cost=0.00..0.00 rows=1 width=265) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: ((profile_id = '00000000-0000-0000-0000-000000000000'::uuid) AND ((product_id)::text = 'product_a'::text))
...

Query and query plan obfuscated, and the query plan continues on and does sequential scan for all future/empty partitions.

Upon observing the query plan I have two questions:

1. Although we specified ORDER BY created_at DES , query plan still scans the partitions in forward chronological order, can it be reversed since it is a backward sort ?

2. We pro-actively created two years worth of future partitions to reduce operational cost. However, since this query does not have the partitioning column created_at in the WHERE clause, it is scanning all the future/empty partitions even after enough records is fetched, basically ignoring the LIMIT clause. How to make it stop scanning when it finds enough records ?

Thank you.

I’ve been mostly reading docs, have not been able to find much insight