Re: wide table, many many partitions, poor query performance

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