partition table slow planning

From: Jatinder Sandhu <jatinder(dot)sandhu(at)flightnetwork(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: partition table slow planning
Date: 2019-07-23 22:46:07
Message-ID: CAE7n=vL-46Ja0bFa2qMxuOgRY-GQOGPQq1AtwDWgmWpRDkcrXA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jatinder Sandhu 2019-07-23 22:48:43 partition table slow planning
Previous Message Tom Lane 2019-07-23 21:49:39 Re: BUG #15922: Simple select with multiple exists filters returns duplicates from a primary key field

Browse pgsql-general by date

  From Date Subject
Next Message Jatinder Sandhu 2019-07-23 22:48:43 partition table slow planning
Previous Message Sergey Koposov 2019-07-23 22:42:45 Re: pg_dump schema in pg11 without global permissions