We carry out different optimization settings to get performance improvement by tuning the various config settings of PostgreSQL. For this experiments, we randomly selected 3 sample attack cases to evaluate various optimization settings. We average our results over 10 runs to account for variability.
We experimented with 4 categories of tuning: default, light tuning, moderate tuning and full tuning. These experiments are summarized as follows:
| Stage | Configuration Focus | Purpose |
|---|---|---|
| A. Default (Vanilla) | No changes | Establish baseline |
| B. Light tuning | Planner + memory settings only | Emulates a reasonable engineer tuning PG casually |
| C. Moderate tuning | Light + cache + parallelism | Represents best-effort user tuning |
| D. Full tuning | All settings incl. WAL/JIT/autovac | Maximize DB performance, but with more system impact |
This is our baseline. The default PostgreSQL configuration is unoptimized and tends to be conservative. By including this category in our experiment, we establish our baseline and serves a meaningful starting point to compare the optimizations’ improvements.
Results:
| Experiment Category | Mode | Avg. Cost | Avg. Execution Time |
|---|---|---|---|
| Vanilla - No Raptor | track | 197,851,757,322,145.12 | 550 |
| Vanilla - No Raptor | search | 9,095.40 | 4,522.42 |
| Vanilla - Raptor | track | 484.26 | 6.76 |
| Vanilla - Raptor | search | 8,312.13 | 4,494.48 |
We introduce light tuning targeting memory, cache, and planner cost estimates.
OPTIMIZED_SETTINGS_LIGHT = (
"SET random_page_cost = 1.1; "
"SET cpu_tuple_cost = 0.005; "
"SET cpu_index_tuple_cost = 0.0025; "
"SET effective_cache_size = '12GB'; "
"SET work_mem = '128MB'; "
"SET default_statistics_target = 200; "
)
Results:
| Experiment Category | Mode | Avg. Cost | Avg. Execution Time |
|---|---|---|---|
| Optimized - No Raptor | track | 72,316,505,058,575.20 | 498.34 |
| Optimized - No Raptor | search | 3,882.48 | 3,841.44 |
| Optimized - Raptor | track | 346.00 | 6.10 |
| Optimized - Raptor | search | 3,391.91 | 4,362.55 |
In addition to the light settings, we add parallelism and collapse control
OPTIMIZED_SETTINGS_MEDIUM = (
OPTIMIZED_SETTINGS_LIGHT +
"SET join_collapse_limit = 12; "
"SET from_collapse_limit = 12; "
"SET parallel_setup_cost = 100; "
"SET parallel_tuple_cost = 0.01; "
)
Results: