RE: speeding up planning with partitions

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 source code.
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 of first 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_plan ones.
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 relcache hash 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 to the 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 after executing a lot of custom plans.

Yoshikazu Imai