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
