Horton

RE: speeding up planning with partitions

Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com>

View thread View on postgr.es
From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
To: "Imai, Yoshikazu" <imai.yoshikazu@jp.fujitsu.com>
Cc: 'Amit Langote' <Langote_Amit_f8@lab.ntt.co.jp>, David Rowley <david.rowley@2ndquadrant.com>, Alvaro Herrera <alvherre@2ndquadrant.com>, "Pg Hackers" <pgsql-hackers@postgresql.org>
Date: 2019-02-06T02:03:34Z
Lists: pgsql-hackers

Attachments

Imai-san,

From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
> On 2019/01/22 18:47, David Rowley wrote:
> > On Tue, 22 Jan 2019 at 20:01, Imai, Yoshikazu
> >> What I understand so far is about 10,000 while loops at total
> (4098+4098+some extra) is needed in hash_seq_search() in EXECUTE query
> after the creation of the generic plan.
> >> 10,000 while loops takes about 10 microsec (of course, we can't estimate
> correct time), and the difference of the latency between 5th and 7th EXECUTE
> is about 8 microsec, I currently think this causes the difference.
> >  >
> >> I don't know this problem relates to Amit-san's patch, but I'll continue
> to investigate it.
> >
> > I had another thought... when you're making a custom plan you're only
> > grabbing locks on partitions that were not pruned (just 1 partition in
> > your case), but when making the generic plan, locks will be acquired
> > on all partitions (all 4000 of them). This likely means that when
> > building the generic plan for the first time that the
> > LockMethodLocalHash table is expanded to fit all those locks, and
> > since we never shrink those down again, it'll remain that size for the
> > rest of your run.  I imagine the reason for the slowdown is that
> > during LockReleaseAll(), a sequential scan is performed over the
> > entire hash table. I see from looking at the hash_seq_search() code
> > that the value of max_bucket is pretty critical to how it'll perform.
> > The while ((curElem = segp[segment_ndx]) == NULL) loop will need to
> > run fewer times with a lower max_bucket.
> 
> I too think that that might be behind that slight drop in performance.
> So, it's good to know what one of the performance bottlenecks is when
> dealing with large number of relations in queries.

Can you compare the performance of auto and force_custom_plan again with the attached patch?  It uses PGPROC's LOCALLOCK list instead of the hash table.


Regards
Takayuki Tsunakawa

Commits

  1. Clean up handling of constraint_exclusion and enable_partition_pruning.

  2. Add test case exercising formerly-unreached code in inheritance_planner.

  3. Speed up planning when partitions can be pruned at plan time.

  4. Avoid crash in partitionwise join planning under GEQO.

  5. Avoid passing query tlist around separately from root->processed_tlist.

  6. Build "other rels" of appendrel baserels in a separate step.

  7. Get rid of duplicate child RTE for a partitioned table.

  8. Rearrange make_partitionedrel_pruneinfo to avoid work when we can't prune.

  9. Don't copy PartitionBoundInfo in set_relation_partition_info.

  10. Move building of child base quals out into a new function

  11. Reorganize planner code moved in b60c39759908

  12. Move inheritance expansion code into its own file

  13. Fix inherited UPDATE/DELETE with UNION ALL subqueries.

  14. Rearrange planner to save the whole PlannerInfo (subroot) for a subquery.