>*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.san...@flightnetwork.com> 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?* > > > > > >
