Case: Modifying a Partitioned Table

Symptom

In the following simple SQL statements, the performance bottlenecks exist in the scan operation on the normal_date table:

    
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on normal_date  (cost=0.00..259.00 rows=30 width=12) (actual time=0.100..3.466 rows=30 loops=1)
   Filter: (("time" >= '2022-09-01 00:00:00'::timestamp without time zone) AND ("time" <= '2022-10-01 00:00:00'::timestamp without time zone))
   Rows Removed by Filter: 9970
 Total runtime: 3.587 ms
(4 rows)

Optimization Analysis

Obviously, there are date features in the time column of table data in the service layer, and this meet the features of a partitioned table. Replan the table definition of the normal_date table. Set the time column as a partition key, and month as an interval unit. Define the partitioned table normal_date_part. The modified result is as follows, and the performance is improved by nearly 10 times:

    
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..480.00 rows=30 width=12) (actual time=0.038..0.085 rows=30 loops=1)
   Iterations: 2
   ->  Partitioned Seq Scan on normal_date_part  (cost=0.00..480.00 rows=30 width=12) (actual time=0.049..0.063 rows=30 loops=2)
         Filter: (("time" >= '2022-09-01 00:00:00'::timestamp without time zone) AND ("time" <= '2022-10-01 00:00:00'::timestamp without time zone))
         Rows Removed by Filter: 31
         Selected Partitions:  3..4
 Total runtime: 0.360 ms
(7 rows)