RE: speeding up planning with partitions - Mailing list pgsql-hackers

I measured the latency of queries executed before and after creating generic plan with master + v15-patch.

In this test, table is partitioned into 4k partitions.
I executed 400,0001 queries by pgbench.
I changed the timing of creating generic plan at 1st, 10,001st, 20,001st, 50,001st, ..., 390,001st by changing the
sourcecode.
 
I run the test with setting both plan_cache_mode = auto and plan_cache_mode = force_custom_plan.

The results is below.
The value in before columns is showing the average latency of queries before creating generic plan in microsec. 
The value in after columns is showing the average latency of queries after creating generic plan in microsec.

[auto]
time of creating generic plan  | before[usec]  |  after[usec]
                          1st             531             142
                     10,001st             144             141
                     20,001st             141             144
                     50,001st             133             140
                    200,001st             131             143
                    390,001st             130             138


[force_custom_plan]
time of creating generic plan  | before[usec]  |  after[usec]
                          1st            
                     10,001st             144             129
                     20,001st             137             131
                     50,001st             133             134
                    200,001st             131             133
                    390,001st             132             131

* A generic plan is actually not created with plan_cache_mode = force_custom_plan.


Looking at the results of force_custom_plan, the latency of first 10,000 transactions is 144 microsec, and the latency
offirst 50,000 transactions is 133 microsec. 
 
I think that is because in the early transactions, relcache hash table is not hot (as David mentioned?).

Comparing the latencies in after column between auto and force_custom_plan, auto ones are higher about 8% than
force_custom_planones.
 
That is, it seems creating generic plan affects the latency of queries executed after creating generic plan.


On Mon, Jan 21, 2019 at 1:32 AM, David Rowley wrote:
> It would be interesting to see the profiles of having the generic plan
> being built on the 6th execution vs the 400,000th execution.
> 
> I'd thought maybe one difference would be the relcache hash table
> having been expanded greatly after the generic plan was created

Does it mean that in the executing queries after the generic plan was created, the time of searching entry in the
relcachehash table becomes slow and it increases the latency?
 

> but I
> see even the generic plan is selecting a random partition, so the
> cache would have ended up with that many items eventually anyway, and
> since we're talking in the odds of 7.8k TPS with 4k partitions, it
> would have only have taken about 2-3 seconds out of the 60 second run
> to hit most or all of those partitions anyway.

And does it mean even if we executes a lot of custom plan without creating generic plan, cache would have been ended up
tothe same size of which is after creating generic plan?
 


Anyway, I'll check the relcache size.
Since I don't know how to get profile at the just time of building generic plan, I'll use
MemoryContextStats(MemoryContext*)function to check the relcache size at before/after building generic plan and at
afterexecuting a lot of custom plans.
 



Yoshikazu Imai