Re: partition table slow planning - Mailing list pgsql-general

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

Book @ FlightNetwork | Check out our Blog | Like us on Facebook