Re: partition table slow planning

PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit'
Total number of partition is 367

Parent table defination

Table "public.itinerary"
Column | Type | Collation | Nullable |
Default
-------------------+-----------------------------+-----------+----------+---------
flight_query | character varying(50) | | not null |
origin | character varying(5) | | not null |
destination | character varying(5) | | not null |
departure_date | character varying(10) | | not null |
month_day | integer | | not null |
journeys | character varying(10485760) | | not null |
origin_metro | character varying(5) | | |
destination_metro | character varying(5) | | |
Partition key: LIST (month_day)

On Wed, Jul 24, 2019 at 5:16 AM Imre Samu <pella(dot)samu(at)gmail(dot)com> wrote:

> >*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(dot)sandhu(at)flightnetwork(dot)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?*
>>
>>
>>
>>
>>
>>

--
Jatinder Sandhu | Database Administrator
+1-905-460-7955 | 145 King Street West, Toronto, ON M5H 1J8
<https://maps.google.com/?q=145+King+Street+West,+Toronto,+ON+M5H+1J8&entry=gmail&source=g>
*Book @ FlightNetwork * <http://www.flightnetwork.com/>| Check out our
*Blog* <http://www.flightnetwork.com/blog/> | Like us on *Facebook
<http://www.facebook.com/DiscountFlights>*