Message-ID: <2d96b24babb2170916a3556e0a4be5bf620aa6da.camel@cybertec.at>
Subject: Re: Partitions and planner
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: J T <jorge.torralba@gmail.com>, "[ADMIN]" <pgsql-admin@postgresql.org>
Date: Tue, 28 Apr 2020 07:19:16 +0200
In-Reply-To: 
 <CACut7uQ9ipGGztdXpXZcCA-WXeHKudXwX4TOGykfVsxvzYUkrg@mail.gmail.com>
References: 
	<CACut7uQ9ipGGztdXpXZcCA-WXeHKudXwX4TOGykfVsxvzYUkrg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Mon, 2020-04-27 at 16:03 -0700, J T wrote:
> This is an odd question. I hope there is a workaround or feature for this that can use.
> 
> We have a huge table close to a billion rows that we are testing a partition by by hash and sub partitions
> by date range so we can drop the old partitions.
> 
> The hash partition we want for performance but the dat range partition just for maintenance. Having said that.
> On our non partitioned table the execution time is fine and the planning time is down to 4ms.
> However, on the partitioned table the execution time is fast but the planning time jumps to 300ms.
> A change of just a few milliseconds is impactful. But, a change of a few hundred milliseconds is not acceptable.
> 
> Having said all this, is there a way to tell the planner not to plan using the sub partitions and only
> use the primary hash partition? This way we could eliminate the extra planning time used on the sub partitions
> since those sub partitions will ever only be used for pruning data.

The planner *has* to consider the subpartitions, because that's where the actual data is.

It seems like you have too many partitions for the PostgreSQL version you are using.

Consider upgrading to v12, and consider doing away with hash partitioning.
Do you have any evidence that it improves performance in your case?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com