Thanks Imre
On Wed., Jul. 24, 2019, 3:23 p.m. Imre Samu, <pella(dot)samu(at)gmail(dot)com> wrote:
> > PostgreSQL 11.3 ... Total number of partition is 367 .... Partition
> key: LIST
>
> As I know:
> in PG11 "Declarative Partitioning Best Practices"
> *... " The query planner is generally able to handle partition hierarchies
> with up to a few hundred partitions fairly well, provided that typical
> queries allow the query planner to prune all but a small number of
> partitions. Planning times become longer and memory consumption becomes
> higher as more partitions are added." *
> *... **"in this case, it may be better to choose to partition by HASH and
> choose a reasonable number of partitions rather than trying to partition by
> LIST" *
> *... "Never assume that more partitions are better than fewer partitions
> and vice-versa."*
>
> https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES
>
>
> In PG12 - it is more optimal:
>
> *Changes: "Improve performance of many operations on partitioned tables
> (Amit Langote, David Rowley, Tom Lane, Álvaro Herrera) Allow tables with
> thousands of child partitions to be processed efficiently by operations
> that only affect a small number of partitions." *
> https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.5.5
> see more:
> https://www.postgresql.org/message-id/flat/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d(at)lab(dot)ntt(dot)co(dot)jp
>
>
> Imre
>
>
>
>
>
> Jatinder Sandhu <jatinder(dot)sandhu(at)flightnetwork(dot)com> ezt írta (időpont:
> 2019. júl. 24., Sze, 16:40):
>
>> 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>*
>>
>