Thanks Imre On Wed., Jul. 24, 2019, 3:23 p.m. Imre Samu, <pella.s...@gmail.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-cf1ee6862...@lab.ntt.co.jp > > > Imre > > > > > > Jatinder Sandhu <jatinder.san...@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.s...@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.san...@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>* >> >
