Date: Mon, 15 Mar 2021 12:03:35 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: S Bob <sbob@quadratum-braccas.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: wide table, many many partitions, poor query performance
Message-ID: <20210315170334.GR29463@telsasoft.com>
References: <6fc233c8-eba9-b042-5578-525270724bec@quadratum-braccas.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <6fc233c8-eba9-b042-5578-525270724bec@quadratum-braccas.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210315170334.GR29463%40telsasoft.com>
Precedence: bulk

On Mon, Mar 15, 2021 at 10:53:06AM -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

Is plannning time the issue ?
Please show diagnostic output.  You can start from here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

> 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

You didn't say anything about the query, so: yes, maybe.
The partition strategy and key need to be selected to optimize the intended
queries.  Hash partitioning is frequently a mistake.

See also:
https://www.postgresql.org/message-id/20200502140032.GI28974@telsasoft.com
https://www.postgresql.org/message-id/20200602173353.GB30144@telsasoft.com

-- 
Justin