Re: partition table slow planning

> 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>*
>