RE: How to make partitioning scale better for larger numbers ofpartitions - Mailing list pgsql-hackers

From Tsunakawa, Takayuki
Subject RE: How to make partitioning scale better for larger numbers ofpartitions
Date
Msg-id 0A3221C70F24FB45833433255569204D1FA50095@G01JPEXMBYT05
Whole thread Raw
In response to Re: How to make partitioning scale better for larger numbers ofpartitions  (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
List pgsql-hackers
From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> The immediate one I think is to refactor the planner such that the new
> pruning code, that we were able to utilize for SELECT in PG 11, can also
> be used for UPDATE/DELETE.  Refactoring needed to replace the pruning
> algorithm was minimal for SELECT, but not so much for UPDATE/DELETE.
> 
> Once we're able to utilize the new pruning algorithm for all the cases,
> we
> can move on to refactoring to avoid locking and opening of all partitions.
>  As long as we're relying on constraint exclusion for partition pruning,
> which we still do for UPDATE/DELETE, we cannot do that because constraint
> exclusion has to look at each partition individually.
> 
> The UPDATE/DELETE planning for partitioning using huge memory and CPU is
> a
> pretty big issue and refactoring planner to avoid that may be what's
> hardest of all the problems to be solved here.

Thank you.  There seem to be many challenges to address...  As a user and PG developer, I'd be happy to see some wiki
pagethat describes the current performance characteristics in terms of # of partitions, the ideal and reachable
performance,and the challenges to overcome to reach that ideal goal.
 


> If the benchmark contains queries that will need to access just one
> partition, then yes the planning part has is the biggest overhead.
> 
> Execution-time overhead is limited to having an extra, possibly needless,
> Append node, but I know David has patch for that too.

That's good news, thanks.  Our user will perform around a hundred single-row INSERT/SELECT/UPDATE/DELETE statements in
eachtransaction, and those are PREPAREd.  I hope PG 11 (with David's patches) will work well for that workload.  I'll
waitfor Kato-san's pgbench -M prepared result.
 

Regards
Takayuki Tsunakawa






pgsql-hackers by date:

Previous
From: David Rowley
Date:
Subject: Re: How to make partitioning scale better for larger numbers of partitions
Next
From: "Kato, Sho"
Date:
Subject: RE: How to make partitioning scale better for larger numbers ofpartitions