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.gi28...@telsasoft.com https://www.postgresql.org/message-id/20200602173353.gb30...@telsasoft.com -- Justin
