RE: Cache relation sizes? - Mailing list pgsql-hackers

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
manypartitions.
 
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
oftable 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