pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwide table, many many partitions, poor query performance
3+ messages / 3 participants
[nested] [flat]
* wide table, many many partitions, poor query performance
@ 2021-03-15 16:53 S Bob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 3+ messages in thread
From: S Bob @ 2021-03-15 16:53 UTC (permalink / raw)
To: pgsql-performance
All;
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
Thanks in advance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wide table, many many partitions, poor query performance
@ 2021-03-15 17:03 Justin Pryzby <pryzby@telsasoft.com>
parent: S Bob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 3+ messages in thread
From: Justin Pryzby @ 2021-03-15 17:03 UTC (permalink / raw)
To: S Bob <sbob@quadratum-braccas.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: wide table, many many partitions, poor query performance
@ 2021-03-15 17:03 Laurenz Albe <laurenz.albe@cybertec.at>
parent: S Bob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2021-03-15 17:03 UTC (permalink / raw)
To: S Bob <sbob@quadratum-braccas.com>; pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-03-15 17:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-15 16:53 wide table, many many partitions, poor query performance S Bob <sbob@quadratum-braccas.com>
2021-03-15 17:03 ` Justin Pryzby <pryzby@telsasoft.com>
2021-03-15 17:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
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