pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Partitions and planner
2+ messages / 2 participants
[nested] [flat]

* Partitions and planner
@ 2020-04-27 23:03 J T <jorge.torralba@gmail.com>
  2020-04-28 05:19 ` Re: Partitions and planner Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 1 reply; 2+ messages in thread

From: J T @ 2020-04-27 23:03 UTC (permalink / raw)
  To: pgsql-admin

This is an odd question. I hope there is a workaround or feature for this
that can use.

We have a huge table close to a billion rows that we are testing a
partition by by hash and sub partitions by date range so we can drop the
old partitions.

The hash partition we want for performance but the dat range partition just
for maintenance. Having said that.  On our non partitioned table the
execution time is fine and the planning time is down to 4ms. However, on
the partitioned table the execution time is fast but the planning time
jumps to 300ms. A change of just a few milliseconds is impactful. But, a
change of a few hundred milliseconds is not acceptable.

Having said all this, is there a way to tell the planner not to plan using
the sub partitions and only use the primary hash partition? This way we
could eliminate the extra planning time used on the sub partitions since
those sub partitions will ever only be used for pruning data.

-- 
Thanks,

JT
----

Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Partitions and planner
  2020-04-27 23:03 Partitions and planner J T <jorge.torralba@gmail.com>
@ 2020-04-28 05:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 0 replies; 2+ messages in thread

From: Laurenz Albe @ 2020-04-28 05:19 UTC (permalink / raw)
  To: J T <jorge.torralba@gmail.com>; pgsql-admin

On Mon, 2020-04-27 at 16:03 -0700, J T wrote:
> This is an odd question. I hope there is a workaround or feature for this that can use.
> 
> We have a huge table close to a billion rows that we are testing a partition by by hash and sub partitions
> by date range so we can drop the old partitions.
> 
> The hash partition we want for performance but the dat range partition just for maintenance. Having said that.
> On our non partitioned table the execution time is fine and the planning time is down to 4ms.
> However, on the partitioned table the execution time is fast but the planning time jumps to 300ms.
> A change of just a few milliseconds is impactful. But, a change of a few hundred milliseconds is not acceptable.
> 
> Having said all this, is there a way to tell the planner not to plan using the sub partitions and only
> use the primary hash partition? This way we could eliminate the extra planning time used on the sub partitions
> since those sub partitions will ever only be used for pruning data.

The planner *has* to consider the subpartitions, because that's where the actual data is.

It seems like you have too many partitions for the PostgreSQL version you are using.

Consider upgrading to v12, and consider doing away with hash partitioning.
Do you have any evidence that it improves performance in your case?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-04-28 05:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-27 23:03 Partitions and planner J T <jorge.torralba@gmail.com>
2020-04-28 05:19 ` 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