From: Laurenz Albe <laurenz.albe@cybertec.at>
To: S Bob <sbob@quadratum-braccas.com>
To: pgsql-performance@postgresql.org
Subject: Re: wide table, many many partitions, poor query performance
Date: Mon, 15 Mar 2021 18:03:59 +0100
Message-ID: <e948e9adef1e8f30d32571dd32b2ee422a7055f5.camel@cybertec.at> (raw)
In-Reply-To: <6fc233c8-eba9-b042-5578-525270724bec@quadratum-braccas.com>
References: <6fc233c8-eba9-b042-5578-525270724bec@quadratum-braccas.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, sbob@quadratum-braccas.com
Subject: Re: wide table, many many partitions, poor query performance
In-Reply-To: <e948e9adef1e8f30d32571dd32b2ee422a7055f5.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox