Hello,
I also find this proposed feature to be beneficial for performance, especially
when we want to extend or truncate large tables.
As mentioned by David, currently there is a query latency spike when we make
generic plan for partitioned table with many partitions.
I tried to apply Thomas' patch for that use case. Aside from measuring the
planning and execution time,
I also monitored the lseek calls using simple strace, with and without the
patch.
Below are the test results.
Setup 8192 table partitions.
(1) set plan_cache_mode = 'force_generic_plan';
[Without Patch]
prepare select_stmt(int) as select * from t where id = $1;
explain (timing off, analyze) execute select_stmt(8192);
[…]
Planning Time: 1678.680 ms
Execution Time: 643.495 ms
$ strace -p [pid] -e trace=lseek -c
% time seconds usecs/call calls errors syscall
---------------------------------------------------------------------------
100.00 0.017247 1 16385 lseek
[With Patch]
[…]
Planning Time: 1596.566 ms
Execution Time: 653.646 ms
$ strace -p [pid] -e trace=lseek -c
% time seconds usecs/call calls errors syscall
---------------------------------------------------------------------------
100.00 0.009196 1 8192 lseek
It was mentioned in the other thread [1] that creating a generic plan for the
first time is very expensive.
Although this patch did not seem to reduce the cost of planning time for
force_generic_plan,
it seems that number of lseek calls was reduced into half during the first
execution of generic plan.
(2) plan_cache_mode = 'auto’
reset plan_cache_mode; -- resets to auto / custom plan
[Without Patch]
[…]
Planning Time: 768.669 ms
Execution Time: 0.776 ms
$ strace -p [pid] -e trace=lseek -c
% time seconds usecs/call calls errors syscall
---------------------------------------------------------------------------
100.00 0.015117 2 8193 lseek
[With Patch]
[…]
Planning Time: 181.690 ms
Execution Time: 0.615 ms
$ strace -p [pid] -e trace=lseek -c
[…]
NO (zero) lseek calls.
Without the patch, there were around 8193 lseek calls.
With the patch applied, there were no lseek calls when creating the custom plan.
(3) set plan_cache_mode = 'force_generic_plan';
-- force it to generic plan again to use the cached plan (no re-planning)
[Without Patch]
[…]
Planning Time: 14.294 ms
Execution Time: 601.141 ms
$ strace -p [pid] -e trace=lseek -c
% time seconds usecs/call calls errors syscall
---------------------------------------------------------------------------
0.00 0.000000 0 1 lseek
[With Patch]
[…]
Planning Time: 13.976 ms
Execution Time: 570.518 ms
$ strace -p [pid] -e trace=lseek -c
[…]
NO (zero) lseek calls.
----
If I did the test correctly, I am not sure though as to why the patch did not
affect the generic planning performance of table with many partitions.
However, the number of lseek calls was greatly reduced with Thomas’ patch.
I also did not get considerable speed up in terms of latency average using
pgbench –S (read-only, unprepared).
I am assuming this might be applicable to other use cases as well.
(I just tested the patch, but haven’t dug up the patch details yet).
Would you like to submit this to the commitfest to get more reviews for
possible idea/patch improvement?
[1]
https://www.postgresql.org/message-id/flat/CAEepm%3D3SSw-Ty1DFcK%3D1rU-K6GSzYzfdD4d%2BZwapdN7dTa6%3DnQ%40mail.gmail.com
Regards,
Kirk Jamison
