Thread: partition table slow planning

partition table slow planning

From

Jatinder Sandhu

Date:

24 July 2019, 01:46:07

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

From

Dilip Kumar

Date:

24 July 2019, 08:35:03

On Wed, Jul 24, 2019 at 4:24 AM Jatinder Sandhu
<jatinder.sandhu@flightnetwork.com> wrote:
>
>
> We encounter a issue when we do query on partition table directly with proper partition key provide. postgres able to
findproblem 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'
ANDmonth_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?
>
I guess when you give the query on the parent table,  based on your
clause it need to search which partition to scan that can increase the
planning time.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com