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)