Message-ID: <e948e9adef1e8f30d32571dd32b2ee422a7055f5.camel@cybertec.at>
Subject: Re: wide table, many many partitions, poor query performance
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: S Bob <sbob@quadratum-braccas.com>, pgsql-performance@postgresql.org
Date: Mon, 15 Mar 2021 18:03:59 +0100
In-Reply-To: <6fc233c8-eba9-b042-5578-525270724bec@quadratum-braccas.com>
References: <6fc233c8-eba9-b042-5578-525270724bec@quadratum-braccas.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/e948e9adef1e8f30d32571dd32b2ee422a7055f5.camel%40cybertec.at>
Precedence: bulk

On Mon, 2021-03-15 at 10:53 -0600, S Bob wrote:
> We have a client that is running PostgreSQL 12, they have a table with 
> 212 columns and 723 partitions
> 
> It seems the planning time is consumed by generating 723 sub plans
> 
> I suspect it's due to the fact that they are using hash based 
> partitioning, example:
> 
> CREATE TABLE rental_transaction_hash_p723 PARTITION OF 
> rental_transaction FOR VALUES WITH (MODULUS 723, REMAINDER 723);
> 
> Based on a strategy like this, queries will ALWAYS scan all partitions 
> unless a hash value is specified as part of the query, correct? I 
> suspect this is the issue... looking for confirmation, or feedback if 
> i'm off base

That is correct.

The only use I can see in hash partitioning is to put the partitions
on different storage devices in order to spread I/O - kind of striping
on the database level.

Unless you can benefit from that, your queries will become slower.

Yours,
Laurenz Albe