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?*
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 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 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>*
Re: partition table slow planning
Thanks Imre On Wed., Jul. 24, 2019, 3:23 p.m. Imre Samu, 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-cf1ee6862...@lab.ntt.co.jp > > > Imre > > > > > > Jatinder Sandhu 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 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 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 = 10
