GitHub user roseduan added a comment to the discussion: [Ideas] Better parallel scans for AO and AOCS tables
I ran the TPC-DS tests for the new design of AOCS parallel scan in branch: https://github.com/apache/cloudberry/pull/2188 Here is the result: ## 1. Testing Environment ### 1.1 Hardware and dataset | Item | Value | |---|---| | Cluster | 8 primary segments, no mirrors | | CPU | 96 cores | | Dataset | TPC-DS SF1000 | | Table storage | `ao_column`, `zstd` level 5, blocksize 32768, no partitioning | ### 1.2 GUC settings #### Layer 1 — cluster level (`postgresql.conf`, identical on all 8 segments) | GUC | Value | Built-in default | Context | |---|---|---|---| | `gp_resqueue_priority_cpucores_per_segment` | `24` | `4` | postmaster | | `gp_appendonly_insert_files` | `4` | `0` | user | | `gp_vmem_protect_limit` | `24000` | `8192` | postmaster | | `statement_mem` | `1048576` kB (1 GB) | `128000` kB | user | | `max_statement_mem` | `20971520` kB (20 GB) | `2048000` kB | superuser | | `work_mem` | `524288` kB (512 MB) | `32768` kB | user | | `shared_buffers` | `98304` × 32 kB (3 GB) | `16384` × 32 kB (512 MB) | postmaster | | `max_parallel_workers` | `64` | `8` | user | | `max_worker_processes` | `64` | `13` | postmaster | | `gp_enable_runtime_filter_pushdown` | `on` | `off` | user | | `autovacuum` | `off` | `on` | sighup | | `max_connections` | `100` | `200` | postmaster | | `max_prepared_transactions` | `100` | `50` | postmaster | | `gp_interconnect_fc_method` | `loss_advance` | `loss` | user | | `gp_interconnect_queue_depth` | `16` | `4` | user | | `gp_interconnect_snd_queue_depth` | `16` | `2` | user | | `gp_segment_connect_timeout` | `1800` s | `180` s | user | | `gp_fts_probe_interval` | `600` s | `60` s | sighup | | `gp_fts_probe_timeout` | `600` s | `20` s | sighup | | `log_statement` | `all` | `none` | superuser | | `listen_addresses` | `*` | `localhost` | postmaster | #### Layer 2 — injected per run via `PGOPTIONS` ```bash export PGOPTIONS="-c enable_parallel=on -c max_parallel_workers_per_gather=$W -c max_parallel_workers=64" ``` | GUC | Value | |---|---| | `enable_parallel` | `on` | | `max_parallel_workers_per_gather` | `4` or `6` (the variable under test) | | `max_parallel_workers` | `64` | #### Effective values inside a running query | GUC | Effective value | |---|---| | `enable_parallel` | `on` | | `max_parallel_workers_per_gather` | `6` | | `max_parallel_workers` | `64` | | `max_worker_processes` | `64` | | `statement_mem` | `1048576` kB (1 GB) | | `work_mem` | `524288` kB (512 MB) | | `optimizer` | `on` *(default)* | | `optimizer_enable_parallel_append` | `off` *(default)* | | `min_parallel_table_scan_size` | `256` ×32 kB = 8 MB *(default)* | | `parallel_setup_cost` | `1000` *(default)* | | `parallel_tuple_cost` | `0.1` *(default)* | | `gp_enable_runtime_filter_pushdown` | `on` | ## 2. Per-query timing Seconds. `Δ%` is Rows-Range Chunk relative to Segment-File; negative means faster. **W=4 columns are single runs; W=6 columns are medians of 3 runs each.** | query | W=4 Segment-File | W=4 Rows-Range | Δ% | W=6 Segment-File | W=6 Rows-Range | Δ% | |---|---:|---:|---:|---:|---:|---:| | q1 | 11 | 10 | -9.1% | 10 | 9 | -10.0% | | q2 | 60 | 45 | -25.0% | 46 | 33 | -28.3% | | q3 | 18 | 18 | +0.0% | 17 | 14 | -17.6% | | q4 | 232 | 214 | -7.8% | 172 | 203 | +18.0% | | q5 | 118 | 90 | -23.7% | 87 | 91 | +4.6% | | q6 | 18 | 17 | -5.6% | 17 | 13 | -23.5% | | q7 | 57 | 42 | -26.3% | 56 | 38 | -32.1% | | q8 | 18 | 19 | +5.6% | 19 | 14 | -26.3% | | q9 | 98 | 101 | +3.1% | 98 | 102 | +4.1% | | q10 | 24 | 25 | +4.2% | 24 | 23 | -4.2% | | q11 | 112 | 138 | +23.2% | 92 | 113 | +22.8% | | q12 | 4 | 5 | +25.0% | 5 | 4 | -20.0% | | q13 | 43 | 40 | -7.0% | 40 | 30 | -25.0% | | q14 | 247 | 248 | +0.4% | 246 | 249 | +1.2% | | q15 | 14 | 10 | -28.6% | 12 | 9 | -25.0% | | q16 | 41 | 43 | +4.9% | 41 | 37 | -9.8% | | q17 | 64 | 52 | -18.8% | 60 | 39 | -35.0% | | q18 | 26 | 24 | -7.7% | 25 | 21 | -16.0% | | q19 | 21 | 22 | +4.8% | 22 | 16 | -27.3% | | q20 | 8 | 9 | +12.5% | 8 | 7 | -12.5% | | q21 | 9 | 6 | -33.3% | 9 | 5 | -44.4% | | q22 | 14 | 14 | +0.0% | 15 | 13 | -13.3% | | q23 | 786 | 816 | +3.8% | 714 | 748 | +4.8% | | q24 | 80 | 78 | -2.5% | 76 | 52 | -31.6% | | q25 | 62 | 60 | -3.2% | 80 | 43 | -46.2% | | q26 | 30 | 23 | -23.3% | 28 | 19 | -32.1% | | q27 | 53 | 42 | -20.8% | 55 | 35 | -36.4% | | q28 | 40 | 39 | -2.5% | 40 | 31 | -22.5% | | q29 | 54 | 50 | -7.4% | 49 | 38 | -22.4% | | q30 | 4 | 5 | +25.0% | 5 | 5 | +0.0% | | q31 | 36 | 33 | -8.3% | 35 | 30 | -14.3% | | q32 | 16 | 17 | +6.2% | 17 | 13 | -23.5% | | q33 | 30 | 32 | +6.7% | 31 | 29 | -6.5% | | q34 | 25 | 24 | -4.0% | 26 | 18 | -30.8% | | q35 | 29 | 29 | +0.0% | 32 | 30 | -6.2% | | q36 | 31 | 32 | +3.2% | 30 | 25 | -16.7% | | q37 | 13 | 11 | -15.4% | 13 | 8 | -38.5% | | q38 | 79 | 83 | +5.1% | 75 | 77 | +2.7% | | q39 | 13 | 15 | +15.4% | 13 | 12 | -7.7% | | q40 | 17 | 15 | -11.8% | 18 | 11 | -38.9% | | q41 | 0 | 1 | — | 0 | 0 | — | | q42 | 18 | 18 | +0.0% | 18 | 14 | -22.2% | | q43 | 25 | 25 | +0.0% | 25 | 19 | -24.0% | | q44 | 23 | 25 | +8.7% | 23 | 25 | +8.7% | | q45 | 7 | 8 | +14.3% | 8 | 7 | -12.5% | | q46 | 31 | 33 | +6.5% | 32 | 25 | -21.9% | | q47 | 52 | 52 | +0.0% | 51 | 39 | -23.5% | | q48 | 35 | 36 | +2.9% | 36 | 29 | -19.4% | | q49 | 44 | 39 | -11.4% | 46 | 34 | -26.1% | | q50 | 31 | 32 | +3.2% | 31 | 25 | -19.4% | | q51 | 91 | 90 | -1.1% | 84 | 80 | -4.8% | | q52 | 17 | 18 | +5.9% | 17 | 14 | -17.6% | | q53 | 21 | 21 | +0.0% | 21 | 16 | -23.8% | | q54 | 39 | 36 | -7.7% | 36 | 26 | -27.8% | | q55 | 17 | 19 | +11.8% | 17 | 14 | -17.6% | | q56 | 27 | 28 | +3.7% | 28 | 26 | -7.1% | | q57 | 25 | 25 | +0.0% | 27 | 20 | -25.9% | | q58 | 25 | 24 | -4.0% | 24 | 23 | -4.2% | | q59 | 229 | 235 | +2.6% | 231 | 196 | -15.2% | | q60 | 29 | 29 | +0.0% | 30 | 27 | -10.0% | | q61 | 42 | 37 | -11.9% | 38 | 36 | -5.3% | | q62 | 13 | 13 | +0.0% | 14 | 10 | -28.6% | | q63 | 21 | 23 | +9.5% | 21 | 17 | -19.0% | | q64 | 84 | 80 | -4.8% | 79 | 69 | -12.7% | | q65 | 153 | 154 | +0.7% | 155 | 165 | +6.5% | | q66 | 22 | 21 | -4.5% | 20 | 18 | -10.0% | | q67 | 368 | 384 | +4.3% | 340 | 364 | +7.1% | | q68 | 65 | 49 | -24.6% | 66 | 41 | -37.9% | | q69 | 25 | 26 | +4.0% | 26 | 23 | -11.5% | | q70 | 68 | 59 | -13.2% | 65 | 46 | -29.2% | | q71 | 47 | 40 | -14.9% | 46 | 32 | -30.4% | | q72 | 66 | 46 | -30.3% | 55 | 34 | -38.2% | | q73 | 21 | 22 | +4.8% | 22 | 16 | -27.3% | | q74 | 79 | 80 | +1.3% | 66 | 69 | +4.5% | | q75 | 90 | 86 | -4.4% | 83 | 69 | -16.9% | | q76 | 21 | 21 | +0.0% | 21 | 17 | -19.0% | | q77 | 32 | 34 | +6.2% | 33 | 32 | -3.0% | | q78 | 347 | 331 | -4.6% | 323 | 319 | -1.2% | | q79 | 41 | 43 | +4.9% | 41 | 34 | -17.1% | | q80 | 47 | 50 | +6.4% | 52 | 44 | -15.4% | | q81 | 8 | 7 | -12.5% | 7 | 7 | +0.0% | | q82 | 14 | 15 | +7.1% | 16 | 12 | -25.0% | | q83 | 5 | 5 | +0.0% | 4 | 5 | +25.0% | | q84 | 4 | 3 | -25.0% | 3 | 3 | +0.0% | | q85 | 11 | 12 | +9.1% | 11 | 9 | -18.2% | | q86 | 10 | 10 | +0.0% | 10 | 8 | -20.0% | | q87 | 75 | 76 | +1.3% | 74 | 78 | +5.4% | | q88 | 21 | 22 | +4.8% | 21 | 16 | -23.8% | | q89 | 21 | 24 | +14.3% | 22 | 18 | -18.2% | | q90 | 6 | 6 | +0.0% | 5 | 4 | -20.0% | | q91 | 3 | 3 | +0.0% | 3 | 3 | +0.0% | | q92 | 10 | 10 | +0.0% | 10 | 8 | -20.0% | | q93 | 31 | 36 | +16.1% | 37 | 26 | -29.7% | | q94 | 21 | 20 | -4.8% | 21 | 20 | -4.8% | | q95 | 348 | 392 | +12.6% | 318 | 354 | +11.3% | | q96 | 23 | 21 | -8.7% | 28 | 15 | -46.4% | | q97 | 187 | 176 | -5.9% | 189 | 181 | -4.2% | | q98 | 20 | 19 | -5.0% | 19 | 13 | -31.6% | | q99 | 25 | 24 | -4.0% | 25 | 17 | -32.0% | | **Total** | **5936** | **5870** | **-1.1%** | **5632** | **5218** | **-7.4%** | ## 3. Total Time Result | Parallelism | Per-run totals | Median | Mean | Std dev | Range | |---|---|---:|---:|---:|---:| | Segment-File | 5689 / 5701 / 5633 s | **5689 s** | 5674 s | 36 s (0.64%) | 68 s (1.2%) | | Rows-Range Chunk | 5217 / 5213 / 5203 s | **5213 s** | 5211 s | 7 s (0.14%) | 14 s (0.27%) | | **Difference** | | **-8.4%** | **-8.2%** | | | ### W=4 vs W=6 | Comparison | W=4 (single run) | W=6 (median of 3) | |---|---:|---:| | Segment-File | 5936 s | 5689 s | | Rows-Range Chunk | 5870 s | 5213 s | | Difference | **-1.1%** | **-8.4%** | ## 4. Overall We can clearly see that the difference between 4 workers is minimal; however, if we increase the number of workers to 6, the performance of the new design improves by nearly 8%. If we have enough hardware resources to increase more parallel workers, I think the performance improvement may be greater. Anyway, you can run the full tests in your local env and set appropriate GUC according to your hardware environment. Feel free to give me any feedback. Thanks! @Alena0704 @yjhjstz @my-ship-it GitHub link: https://github.com/apache/cloudberry/discussions/2186#discussioncomment-19495959 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
