pgjdbc/pgjdbc GitHub issues and pull requests (mirror)help / color / mirror / Atom feed
[pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts 4+ messages / 3 participants [nested] [flat]
* [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts @ 2024-04-08 11:02 "vlsi (@vlsi)" <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: vlsi (@vlsi) @ 2024-04-08 11:02 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> **Describe the issue** Currently we rewrite the select to multi-values insert. It requires multiple statements, and we use power-of-two packing to alleviate the overhead. There is an alternative option: ```sql insert into table(id, name, ...) select unnest(?), unnest(?), ... ``` Then we could bind the values as a set of arrays. It might have the following improvements: 1) We would no longer require "power-of-two" logic 2) The query will be shorter 3) It might be faster 4) There will be no limit of "65535 binds" TODO: * Measure how `select unnest(?), unnest(?)` compares with `values(...), (...), (...)` * Test if it would work with "on conflict", "merge" and so on * Figure out if we could support `values(DEFAULT, ...)`. It might be `DEFAULT` would require adjusting the column list in the `into` clause * Figure out if we could support expressions in `values` like `values(?+?, ?-?)` ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts @ 2024-05-02 18:59 "davecramer (@davecramer)" <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: davecramer (@davecramer) @ 2024-05-02 18:59 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> This deserves some study for sure ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts @ 2025-08-22 12:40 "lantalex (@lantalex)" <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: lantalex (@lantalex) @ 2025-08-22 12:40 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> Some results from my local machine (with this change: https://github.com/pgjdbc/pgjdbc/pull/3782) `Benchmark (p1nrows) (p2multi) Mode Cnt Score Error Units InsertBatch.insertBatch 16 1 avgt 50 1.727 ± 0.023 ms/op InsertBatch.insertBatch 16 4 avgt 50 1.658 ± 0.030 ms/op InsertBatch.insertBatch 16 8 avgt 50 1.623 ± 0.032 ms/op InsertBatch.insertBatch 16 16 avgt 50 1.605 ± 0.029 ms/op InsertBatch.insertBatch 16 128 avgt 50 1.616 ± 0.041 ms/op InsertBatch.insertBatch 128 1 avgt 50 2.725 ± 0.089 ms/op InsertBatch.insertBatch 128 4 avgt 50 2.421 ± 0.032 ms/op InsertBatch.insertBatch 128 8 avgt 50 2.277 ± 0.055 ms/op InsertBatch.insertBatch 128 16 avgt 50 2.137 ± 0.035 ms/op InsertBatch.insertBatch 128 128 avgt 50 2.078 ± 0.023 ms/op InsertBatch.insertBatch 1024 1 avgt 50 15.202 ± 0.625 ms/op InsertBatch.insertBatch 1024 4 avgt 50 6.122 ± 0.301 ms/op InsertBatch.insertBatch 1024 8 avgt 50 3.109 ± 0.181 ms/op InsertBatch.insertBatch 1024 16 avgt 50 3.180 ± 0.198 ms/op InsertBatch.insertBatch 1024 128 avgt 50 3.172 ± 0.172 ms/op InsertBatch.insertBatchWithRewrite 16 1 avgt 50 1.597 ± 0.018 ms/op InsertBatch.insertBatchWithRewrite 16 4 avgt 50 1.600 ± 0.026 ms/op InsertBatch.insertBatchWithRewrite 16 8 avgt 50 1.593 ± 0.035 ms/op InsertBatch.insertBatchWithRewrite 16 16 avgt 50 1.608 ± 0.034 ms/op InsertBatch.insertBatchWithRewrite 16 128 avgt 50 1.606 ± 0.033 ms/op InsertBatch.insertBatchWithRewrite 128 1 avgt 50 2.100 ± 0.044 ms/op InsertBatch.insertBatchWithRewrite 128 4 avgt 50 2.129 ± 0.055 ms/op InsertBatch.insertBatchWithRewrite 128 8 avgt 50 2.099 ± 0.043 ms/op InsertBatch.insertBatchWithRewrite 128 16 avgt 50 2.102 ± 0.045 ms/op InsertBatch.insertBatchWithRewrite 128 128 avgt 50 2.056 ± 0.051 ms/op InsertBatch.insertBatchWithRewrite 1024 1 avgt 50 3.334 ± 0.152 ms/op InsertBatch.insertBatchWithRewrite 1024 4 avgt 50 3.383 ± 0.233 ms/op InsertBatch.insertBatchWithRewrite 1024 8 avgt 50 3.286 ± 0.213 ms/op InsertBatch.insertBatchWithRewrite 1024 16 avgt 50 3.128 ± 0.155 ms/op InsertBatch.insertBatchWithRewrite 1024 128 avgt 50 3.210 ± 0.222 ms/op InsertBatch.insertCopy 16 1 avgt 50 25.855 ± 0.389 ms/op InsertBatch.insertCopy 16 4 avgt 50 6.475 ± 0.058 ms/op InsertBatch.insertCopy 16 8 avgt 50 3.279 ± 0.057 ms/op InsertBatch.insertCopy 16 16 avgt 50 1.649 ± 0.012 ms/op InsertBatch.insertCopy 16 128 avgt 50 1.677 ± 0.032 ms/op InsertBatch.insertCopy 128 1 avgt 50 208.746 ± 3.734 ms/op InsertBatch.insertCopy 128 4 avgt 50 52.187 ± 0.633 ms/op InsertBatch.insertCopy 128 8 avgt 50 25.740 ± 0.428 ms/op InsertBatch.insertCopy 128 16 avgt 50 13.209 ± 0.190 ms/op InsertBatch.insertCopy 128 128 avgt 50 1.896 ± 0.022 ms/op InsertBatch.insertCopy 1024 1 avgt 50 1651.287 ± 12.337 ms/op InsertBatch.insertCopy 1024 4 avgt 50 414.660 ± 6.796 ms/op InsertBatch.insertCopy 1024 8 avgt 50 209.550 ± 3.395 ms/op InsertBatch.insertCopy 1024 16 avgt 50 105.950 ± 1.746 ms/op InsertBatch.insertCopy 1024 128 avgt 50 15.373 ± 0.278 ms/op InsertBatch.insertExecute 16 1 avgt 50 24.457 ± 0.426 ms/op InsertBatch.insertExecute 128 1 avgt 50 196.709 ± 3.700 ms/op InsertBatch.insertExecute 1024 1 avgt 50 1569.965 ± 29.554 ms/op InsertBatch.insertUnnestArrays 16 1 avgt 50 1.908 ± 0.042 ms/op InsertBatch.insertUnnestArrays 16 4 avgt 50 1.695 ± 0.026 ms/op InsertBatch.insertUnnestArrays 16 8 avgt 50 1.650 ± 0.034 ms/op InsertBatch.insertUnnestArrays 16 16 avgt 50 1.619 ± 0.032 ms/op InsertBatch.insertUnnestArrays 16 128 avgt 50 1.610 ± 0.029 ms/op InsertBatch.insertUnnestArrays 128 1 avgt 50 3.203 ± 0.146 ms/op InsertBatch.insertUnnestArrays 128 4 avgt 50 2.613 ± 0.067 ms/op InsertBatch.insertUnnestArrays 128 8 avgt 50 2.356 ± 0.053 ms/op InsertBatch.insertUnnestArrays 128 16 avgt 50 2.161 ± 0.047 ms/op InsertBatch.insertUnnestArrays 128 128 avgt 50 1.988 ± 0.047 ms/op InsertBatch.insertUnnestArrays 1024 1 avgt 50 15.987 ± 0.890 ms/op InsertBatch.insertUnnestArrays 1024 4 avgt 50 6.338 ± 0.322 ms/op InsertBatch.insertUnnestArrays 1024 8 avgt 50 3.269 ± 0.203 ms/op InsertBatch.insertUnnestArrays 1024 16 avgt 50 2.979 ± 0.129 ms/op InsertBatch.insertUnnestArrays 1024 128 avgt 50 3.013 ± 0.217 ms/op InsertBatch.insertUnnestStruct 16 1 avgt 50 1.819 ± 0.042 ms/op InsertBatch.insertUnnestStruct 16 4 avgt 50 1.651 ± 0.021 ms/op InsertBatch.insertUnnestStruct 16 8 avgt 50 1.630 ± 0.029 ms/op InsertBatch.insertUnnestStruct 16 16 avgt 50 1.604 ± 0.030 ms/op InsertBatch.insertUnnestStruct 16 128 avgt 50 1.610 ± 0.035 ms/op InsertBatch.insertUnnestStruct 128 1 avgt 50 2.705 ± 0.066 ms/op InsertBatch.insertUnnestStruct 128 4 avgt 50 2.442 ± 0.051 ms/op InsertBatch.insertUnnestStruct 128 8 avgt 50 2.204 ± 0.034 ms/op InsertBatch.insertUnnestStruct 128 16 avgt 50 2.079 ± 0.048 ms/op InsertBatch.insertUnnestStruct 128 128 avgt 50 2.018 ± 0.049 ms/op InsertBatch.insertUnnestStruct 1024 1 avgt 50 15.142 ± 0.766 ms/op InsertBatch.insertUnnestStruct 1024 4 avgt 50 5.853 ± 0.266 ms/op InsertBatch.insertUnnestStruct 1024 8 avgt 50 3.095 ± 0.190 ms/op InsertBatch.insertUnnestStruct 1024 16 avgt 50 3.079 ± 0.193 ms/op InsertBatch.insertUnnestStruct 1024 128 avgt 50 3.027 ± 0.151 ms/op` ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts @ 2025-08-22 12:43 "lantalex (@lantalex)" <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: lantalex (@lantalex) @ 2025-08-22 12:43 UTC (permalink / raw) To: pgjdbc/pgjdbc <[email protected]> Some additional results for batch size = 8192 and total rows = 16384 `Benchmark (p1nrows) (p2multi) Mode Cnt Score Error Units InsertBatch.insertBatch 16384 1 avgt 10 210.620 ± 15.459 ms/op InsertBatch.insertBatch 16384 8192 avgt 10 12.740 ± 0.656 ms/op InsertBatch.insertBatchWithRewrite 16384 1 avgt 10 13.450 ± 0.949 ms/op InsertBatch.insertBatchWithRewrite 16384 8192 avgt 10 13.264 ± 0.967 ms/op InsertBatch.insertCopy 16384 1 avgt 10 26194.022 ± 277.634 ms/op InsertBatch.insertCopy 16384 8192 avgt 10 8.687 ± 1.083 ms/op InsertBatch.insertExecute 16384 1 avgt 10 24822.447 ± 251.000 ms/op InsertBatch.insertUnnestArrays 16384 1 avgt 10 194.278 ± 15.689 ms/op InsertBatch.insertUnnestArrays 16384 8192 avgt 10 9.933 ± 0.173 ms/op InsertBatch.insertUnnestStruct 16384 1 avgt 10 192.089 ± 27.568 ms/op InsertBatch.insertUnnestStruct 16384 8192 avgt 10 10.148 ± 0.241 ms/op ` ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-22 12:43 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-08 11:02 [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts "vlsi (@vlsi)" <[email protected]> 2024-05-02 18:59 ` "davecramer (@davecramer)" <[email protected]> 2025-08-22 12:40 ` "lantalex (@lantalex)" <[email protected]> 2025-08-22 12:43 ` "lantalex (@lantalex)" <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox