From: | Imre Samu <pella(dot)samu(at)gmail(dot)com> |
---|---|
To: | Jatinder Sandhu <jatinder(dot)sandhu(at)flightnetwork(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: partition table slow planning |
Date: | 2019-07-24 09:16:05 |
Message-ID: | CAJnEWw=zoVWvUSD1Q3bM9CQyW-Zu7TRwGxtSqOYhXFsdgRayTQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-general |
>*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(dot)sandhu(at)flightnetwork(dot)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?*
>
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Manuel Rigger | 2019-07-24 10:00:11 | ADD CHECK fails for parent table if column used in CHECK is fully-qualified |
Previous Message | jay chauhan | 2019-07-24 08:08:30 | Re: Request for resolution || Support |
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2019-07-24 09:24:23 | Re: Query plan: SELECT vs INSERT from same select |
Previous Message | Cyril Champier | 2019-07-24 08:45:01 | Re: Default ordering option |