Tsunakawa-san, On 2019/03/26 17:21, Tsunakawa, Takayuki wrote: > From: David Rowley [mailto:david.row...@2ndquadrant.com] >> On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut >> <peter.eisentr...@2ndquadrant.com> wrote: >>> Perhaps "speeding up planning with partitions" needs to be accepted first? >> >> Yeah, I think it likely will require that patch to be able to measure >> the gains from this patch. >> >> If planning a SELECT to a partitioned table with a large number of >> partitions using PREPAREd statements, when we attempt the generic plan >> on the 6th execution, it does cause the local lock table to expand to >> fit all the locks for each partition. This does cause the >> LockReleaseAll() to become slow due to the hash_seq_search having to >> skip over many empty buckets. Since generating a custom plan for a >> partitioned table with many partitions is still slow in master, then I >> very much imagine you'll struggle to see the gains brought by this >> patch. > > Thank you David for explaining. Although I may not understand the effect of > "speeding up planning with partitions" patch, this patch takes effect even > without it. That is, perform the following in the same session: > > 1. SELECT count(*) FROM table; on a table with many partitions. That bloats > the LocalLockHash. > 2. PREPARE a point query, e.g., SELECT * FROM table WHERE pkey = $1; > 3. EXECUTE the PREPAREd query repeatedly, with each EXECUTE in a separate > transaction. Without the patch, each transaction's LockReleaseAll() has to > scan the bloated large hash table.
My understanding of what David wrote is that the slowness of bloated hash table is hard to notice, because planning itself is pretty slow. With the "speeding up planning with partitions" patch, planning becomes quite fast, so the bloated hash table overhead and so your patch's benefit is easier to notice. This patch is clearly helpful, but it's just hard to notice it when the other big bottleneck is standing in the way. Thanks, Amit
