Thread: 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
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=#
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?
>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
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 exampleitinerary=# 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 msWhen I do query on directly on the partition table it is quite fastitinerary=# 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)
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
ImreJatinder 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 exampleitinerary=# 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 msWhen I do query on directly on the partition table it is quite fastitinerary=# 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 |
| Book @ FlightNetwork | Check out our Blog | Like us on Facebook |
> PostgreSQL 11.3 ... Total number of partition is 367 .... Partition key: LIST
In PG12 - it is more optimal:
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."
... " 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
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@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 367Parent table definationTable "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
ImreJatinder 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 exampleitinerary=# 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 msWhen I do query on directly on the partition table it is quite fastitinerary=# 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 Book @ FlightNetwork | Check out our Blog | Like us on Facebook
Thanks Imre
On Wed., Jul. 24, 2019, 3:23 p.m. Imre Samu, <pella.samu@gmail.com> wrote:
> PostgreSQL 11.3 ... Total number of partition is 367 .... Partition key: LISTAs 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.5see more: https://www.postgresql.org/message-id/flat/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jpImreJatinder 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 367Parent table definationTable "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
ImreJatinder 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 exampleitinerary=# 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 msWhen I do query on directly on the partition table it is quite fastitinerary=# 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 Book @ FlightNetwork | Check out our Blog | Like us on Facebook