Aurora Limitless - Single and Multi-Shard Scan

I have created a sharded table and will examine the execution plan for point and range queries. First, let's review the execution plan from the router.

postgres_limitless=> set rds_aurora.limitless_explain_options=single_shard_optimization;
SET

A point query, where the shard key matches a specific value, routes to only one shard.

postgres_limitless=> explain (analyze, buffers) 
                     select * from my_sharded_table 
                     where id in (40)
;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) (actual time=1.271..1.272 rows=0 loops=1)
 Single Shard Optimized
 Planning Time: 1.027 ms
 Execution Time: 1.712 ms
(4 rows)

When running a similar query that involves three values, The scan is distributed across multiple shards:

postgres_limitless=> explain (analyze, buffers) 
                     select * from my_sharded_table 
                     where id in (40,41,42)
;
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=100.00..200.01 rows=2 width=12) (actual time=4.222..4.223 rows=0 loops=1)
   ->  Async Foreign Scan on my_sharded_table_fs00001 my_sharded_table_1  (cost=100.00..100.00 rows=1 width=12) (actual time=0.980..0.980 rows=0 loops=1)
   ->  Async Foreign Scan on my_sharded_table_fs00002 my_sharded_table_2  (cost=100.00..100.00 rows=1 width=12) (actual time=3.105..3.105 rows=0 loops=1)
 Planning Time: 0.151 ms
 Execution Time: 20.225 ms
(5 rows)

A range query must scan all shards because it is sharded by hash, and the hash function doesn't preserve the order:


postgres_limitless=> explain (analyze, buffers) 
                     select * from my_sharded_table 
                     where id between 40 and 42
;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=100.00..200.01 rows=2 width=12) (actual time=27.373..27.375 rows=0 loops=1)
   ->  Async Foreign Scan on my_sharded_table_fs00001 my_sharded_table_1  (cost=100.00..100.00 rows=1 width=12) (actual time=5.450..5.450 rows=0 loops=1)
   ->  Async Foreign Scan on my_sharded_table_fs00002 my_sharded_table_2  (cost=100.00..100.00 rows=1 width=12) (actual time=21.035..21.035 rows=0 loops=1)
 Planning Time: 0.145 ms
 Execution Time: 43.222 ms
(5 rows)

As we have seen previously, the shards displayed by the router are only virtual. Each table is further divided into table slices in the shard servers, which can be viewed by adding shard_plans to limitless_explain_options:

postgres_limitless=> set rds_aurora.limitless_explain_options=shard_plans, single_shard_optimization;
SET

A point query reads only one table slice from a single shard, here the table slice 96 located in shard 5:

postgres_limitless=> explain (analyze, buffers) 
                     select * from my_sharded_table 
                     where id in (42)
;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) (actual time=0.823..0.823 rows=0 loops=1)
   Remote Plans from Shard 5:
         Seq Scan on my_sharded_table_ts00096 my_sharded_table  (cost=0.00..0.00 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1)
           Filter: (id = 42)
 Single Shard Optimized
 Planning Time: 0.110 ms
 Execution Time: 1.555 ms
(7 rows)

When querying three values, I read from three table slices, slice 96 from shard 5 and slices 2 and 496 from shard 4:

postgres_limitless=> explain (analyze, buffers) select * from my_sharded_table where id in (40,41,42);
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=100.00..200.01 rows=2 width=12) (actual time=4.241..4.242 rows=0 loops=1)
   ->  Async Foreign Scan on my_sharded_table_fs00001 my_sharded_table_1  (cost=100.00..100.00 rows=1 width=12) (actual time=0.494..0.494 rows=0 loops=1)
         Remote Plans from Shard 5:
               Append  (cost=0.00..0.01 rows=2 width=12) (actual time=0.005..0.005 rows=0 loops=1)
                 ->  Seq Scan on my_sharded_table_ts00096 my_sharded_table_1  (cost=0.00..0.00 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
                       Filter: (id = ANY ('{40,41,42}'::bigint[]))
                 ->  Seq Scan on my_sharded_table_ts00170 my_sharded_table_2  (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
                       Filter: (id = ANY ('{40,41,42}'::bigint[]))
   ->  Async Foreign Scan on my_sharded_table_fs00002 my_sharded_table_2  (cost=100.00..100.00 rows=1 width=12) (actual time=3.159..3.159 rows=0 loops=1)
         Remote Plans from Shard 4:
               Seq Scan on my_sharded_table_ts00496 my_sharded_table  (cost=0.00..0.00 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)
                 Filter: (id = ANY ('{40,41,42}'::bigint[]))
 Planning Time: 0.152 ms
 Execution Time: 20.769 ms
(14 rows)

Because of HASH sharding of the sharding key, the partition pruning we have seen above is possible only when querying on discrete values. A range query must read all partitions from all shards:

postgres_limitless=> explain (analyze, buffers) 
                     select * from my_sharded_table
                     where id<42
;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=100.00..200.01 rows=2 width=12) (actual time=23.527..23.528 rows=0 loops=1)
   ->  Async Foreign Scan on my_sharded_table_fs00001 my_sharded_table_1  (cost=100.00..100.00 rows=1 width=12) (actual time=4.122..4.122 rows=0 loops=1)
         Remote Plans from Shard 5:
               Append  (cost=0.00..1.28 rows=256 width=12) (actual time=0.168..0.198 rows=0 loops=1)
                 ->  Seq Scan on my_sharded_table_ts00001 my_sharded_table_1  (cost=0.00..0.00 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)
                       Filter: (id < 42)
                 ->  Seq Scan on my_sharded_table_ts00002 my_sharded_table_2  (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
                       Filter: (id < 42)
...
                 ->  Seq Scan on my_sharded_table_ts00256 my_sharded_table_256  (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1)
                       Filter: (id < 42)
   ->  Async Foreign Scan on my_sharded_table_fs00002 my_sharded_table_2  (cost=100.00..100.00 rows=1 width=12) (actual time=19.096..19.096 rows=0 loops=1)
         Remote Plans from Shard 4:
               Append  (cost=0.00..1.28 rows=256 width=12) (actual time=0.258..0.286 rows=0 loops=1)
                 ->  Seq Scan on my_sharded_table_ts00257 my_sharded_table_1  (cost=0.00..0.00 rows=1 width=12) (actual time=0.010..0.010 rows=0 loops=1)
                       Filter: (id < 42)
...
                 ->  Seq Scan on my_sharded_table_ts00511 my_sharded_table_255  (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1)
                       Filter: (id < 42)
                 ->  Seq Scan on my_sharded_table_ts00512 my_sharded_table_256  (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1)
                       Filter: (id < 42)
 Planning Time: 0.116 ms
 Execution Time: 40.533 ms
(1033 rows)

I ran this on an empty table, and, looking at the number of table partitions, the execution plan indicates that Aurora Limitless's performance is optimal for single-shard queries. This efficiency is particularly relevant for queries that contain an equality predicate on the sharding key.

Blog

Aurora Limitless - Single and Multi-Shard Scan

Franck Pachot

Join Our Newsletter. No Spam, Only the good stuff.

Related