Re: partition table slow planning

[Imre Samu <pella.samu@xxxxxxxxx>]

>Can we know why this is happening?

Please give us - more info about your system:
- PG version? 
- number of partitions?
- any other important?

for example - in PG 11.2 Changes:
"Improve planning speed for large inheritance or partitioning table groups (Amit Langote, Etsuro Fujita)"
https://www.postgresql.org/docs/current/release-11-2.html

Imre

Jatinder Sandhu <jatinder.sandhu@xxxxxxxxxxxxxxxxx> ezt írta (időpont: 2019. júl. 24., Sze, 9:22):

We encounter a issue when we do query on partition table directly with proper partition key provide. postgres able to find problem partition but when I do explain plan it showing 95% spend on planning the execution . Here is example

itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destination ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
itinerary-# ;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.29..13.79 rows=11 width=1024) (actual time=0.033..0.037 rows=1 loops=1)
   ->  Index Scan using itinerary_101_destination_departure_date_idx on itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual time=0.033..0.036 rows=1 loops=1)
         Index Cond: (((destination)::text = 'GRJ'::text) AND ((departure_date)::text = '2020-01-01'::text))
         Filter: (month_day = 101)
 Planning Time: 51.677 ms
 Execution Time: 0.086 ms

When  I do query on directly on the partition table it is quite fast

itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary_101 WHERE destination ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
itinerary-# ;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using itinerary_101_destination_departure_date_idx on itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual time=0.043..0.048 rows=1 loops=1)
   Index Cond: (((destination)::text = 'GRJ'::text) AND ((departure_date)::text = '2020-01-01'::text))
   Filter: (month_day = 101)
 Planning Time: 0.191 ms
 Execution Time: 0.074 ms
(5 rows)

itinerary=#

Can we know why this is happening?