[Bug] TPC-DS 1000G, statement_mem=1G, query performance is not as expected

[congxuebin]

Apache Cloudberry version

main branch, commit:1cbab9b3

What happened

The following is performance report with more details:

Hashdata Performance Test Report.htm.zip

What you think should happen instead

We would expect the total query execution time to be around 2,200 seconds according to the baseline result, but the actual query time is around 2,400 seconds

No response

How to reproduce

GUC Setting:

20250424:13:31:55:1943472 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c optimizer -v on'
20250424:13:31:56:1944554 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c optimizer_analyze_root_partition -v on --masteronly'
20250424:13:31:57:1944650 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_autostats_mode -v none --masteronly'
20250424:13:31:59:1944746 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c default_statistics_target -v 100'
20250424:13:32:01:1945809 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_vmem_protect_limit -v 15000'
20250424:13:32:03:1946872 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c max_statement_mem -v 14GB'
20250424:13:32:05:1947935 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c statement_mem -v 1GB'
20250424:13:32:07:1948998 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_fts_probe_timeout -v 600'
20250424:13:32:09:1950064 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_fts_probe_interval -v 600'
20250424:13:32:11:1951127 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_segment_connect_timeout -v 1800 -m 1800'
20250424:13:32:13:1952190 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_interconnect_queue_depth -v 10'
20250424:13:32:15:1953253 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_interconnect_snd_queue_depth -v 8'
20250424:13:32:17:1954315 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c gp_interconnect_min_retries_before_timeout -v 100'
20250424:13:32:20:1955377 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c autovacuum -v off'
20250424:13:32:22:1956442 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c max_connections -v 500 -m 100'
20250424:13:32:24:1957553 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c max_prepared_transactions -v 100'
20250424:13:32:26:1958616 gpconfig:systest106:gpadmin-[INFO]:-completed successfully with parameters '-c work_mem -v 512MB'

run drop cache on each node before test every time:

sync; echo 3 > /proc/sys/vm/drop_caches 

export CHIP_TYPE="x86"
export ENABLE_GUC_CONFIGURATION="true"
export GEN_DATA_SCALE="1000"
export MULTI_USER_COUNT="1"
# step 00_compile_tpcds
export RUN_COMPILE_TPCDS="true"
# step 01_gen_data
export RUN_GEN_DATA="true"
export GEN_NEW_DATA="true"
# step 02_init
export RUN_INIT="true"
# step 03_ddl
export RUN_DDL="true"
export DROP_EXISTING_TABLES="true"
# step 04_load
export RUN_LOAD="true"
# step 05_sql
export RUN_SQL="true"
export RUN_ANALYZE="true"
export QUERY_INTERVAL="1"
# set ON_ERROR_STOP to 1 if you want to stop when error occurs
export ON_ERROR_STOP="0"
# step 06_single_user_reports
export RUN_SINGLE_USER_REPORTS="true"
# step 07_multi_user
export RUN_MULTI_USER="false"
export RUN_QGEN="false"
# step 08_multi_user_reports
export RUN_MULTI_USER_REPORTS="false"
# set this to true if binary location changed
export RESET_ENV_ON_SEGMENT='false'
export RUN_SCORE="false"
## misc options
export GREENPLUM_PATH=$GPHOME/greenplum_path.sh
export ADMIN_USER="${USER}"
export SINGLE_USER_ITERATIONS="1"
export EXPLAIN_ANALYZE="true"
export RANDOM_DISTRIBUTION='false'
export STATEMENT_MEM="1GB"
export STATEMENT_MEM_MULTI_USER="1GB"
export GPFDIST_LOCATION="p"
export OSVERSION=$(uname)
export MASTER_HOST=$(hostname -s)
export LD_PRELOAD=/lib64/libz.so.1 ps
## storage options 
export SMALL_STORAGE="appendonly=true, orientation=column, compresstype=zstd, compresslevel=5, blocksize=1048576"
export MEDIUM_STORAGE="appendonly=true, orientation=column, compresstype=zstd, compresslevel=5, blocksize=1048576"
export LARGE_STORAGE="appendonly=true, orientation=column, compresstype=zstd, compresslevel=5, blocksize=1048576"

Operating System

Oracle Linux 9.5

Anything else

No response

Are you willing to submit PR?

• [ ] Yes, I am willing to submit a PR!

Code of Conduct

• [x] I agree to follow this project's Code of Conduct.