partition table slow planning

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

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.samu@gmail.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.sandhu@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?*

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

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:
/messages/by-id/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp

Imre

Jatinder Sandhu <jatinder.sandhu@flightnetwork.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.samu@gmail.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.sandhu@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?*

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