performance: `tuplehash_iterate` and `group by` question, very slow
VERSION: PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit
SYSTEAM KERNEL: Linux localhost.localdomain 5.14.0-362.13.1.el9_3.x86_64 #1
SMP PREEMPT_DYNAMIC Wed Dec 13 14:07:45 UTC 2023 x86_64 x86_64 x86_64
GNU/Linux
SYSTEAM INFO: Rocky Linux 9.3 (Blue Onyx)
CPU: Intel(R) Xeon(R) w5-2445
MOMROY: 256GB
I have a 460 GB table.
+------------------+--------------------------+-----------+
| Column | Type | Modifiers |
|------------------+--------------------------+-----------|
| sha_256 | bytea | not null |
| aaaaa_id | bigint | not null |
| bbbbb_timestamp | timestamp with time zone | not null |
| cccccc_id | integer | not null |
| ddddddddddddddd | cert_purpose_type | not null |
+------------------+--------------------------+-----------+I run the query:
```sql
SELECT
l1.cccccc_id,
l1.count
FROM (
SELECT
cccccc_id,
count(aaaaa_id) count
FROM (
SELECT
cccccc_id,
aaaaa_id
FROM
some_table cle
GROUP BY
cccccc_id,
aaaaa_id) cle_data
GROUP BY
cccccc_id) l1;
```
It's long time run, more than 25 hours, CPU keep 95.6%.
My table were create by timescaledb, the query first stage run about 2
hours , and 243.17G temp file created.
I d'not know what's happened, And how I debug or performance this question.
And who had the same question?
```text
PostgreSQL 16.2 - localhost.localdomain - xxxx@localhost:5432/xxxx - Ref.:
2s - Duration mode: query
* Global: 1 day, 22 hours and 13 minutes uptime, 5.38T dbs size - 0B/s
growth, - cache hit ratio, 0.00% rollback ratio
Sessions: 6/800 total, 2 active, 4 idle, 0 idle in txn, 0 idle in txn
abrt, 0 waiting
Activity: 3 tps, 0 insert/s, 0 update/s, 0 delete/s, 0 tuples
returned/s, 244 temp files, 243.17G temp size
* Worker processes: 0/16 total, 0/4 logical workers, 0/16 parallel workers
Other processes & info: 0/8 autovacuum workers, 0/0 wal senders, 0 wal
receivers, 0/10 repl. slots
* Mem.: 250.85G total, 20.69G (8.25%) free, 18.60G (7.42%) used, 211.55G
(84.34%) buff+cached
Swap: 0B total, 0B (-) free, 0B (-) used
IO: 1/s max iops, 0B/s - 0/s read, 7.93K/s - 1/s write
Load average: 1.11 1.11 1.15
PID DATABASE APP USER CLIENT
CPU% MEM% READ/s WRITE/s TIME+ Waiting IOW
state Query
522417 some_db psql some_user 127.0.0.1 95.6
21.1 0B 7.93K 25 h N active
xxxx
```
I run the `perf top -p 522417` monitor the thread, the `tuplehash_iterate`
use 98.79% CPU cycles:
```text
Samples: 327K of event 'cycles', 4000 Hz, Event count (approx.):
64246693339 lost: 0/0 drop: 0/0
Overhead Shared Object Symbol
98.79% postgres [.] tuplehash_iterate
0.94% libc.so.6 [.] __memset_avx512_unaligned_erms
0.03% [kernel] [k] sync_regs
0.02% [kernel] [k] perf_adjust_freq_unthr_context
0.01% [kernel] [k] nohz_balancer_kick
0.01% [kernel] [k] timekeeping_advance
0.01% [kernel] [k] arch_asym_cpu_priority
0.01% [kernel] [k] arch_scale_freq_tick
0.01% [kernel] [k] _raw_spin_lock_irqsave
0.01% [kernel] [k] _raw_spin_lock
0.01% [kernel] [k] __intel_pmu_enable_all.constprop.0
0.01% [kernel] [k] cpuacct_account_field
0.01% [kernel] [k] update_cfs_group
0.01% [kernel] [k] cpuacct_charge
0.01% [kernel] [k] native_sched_clock
0.00% [kernel] [k] lapic_next_deadline
0.00% [kernel] [k] __run_timers.part.0
0.00% [kernel] [k] ktime_get_update_offsets_now
......
```
```text
+-----------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN
|
|-----------------------------------------------------------------------------------------------------------------------|
| Subquery Scan on l1 (cost=167901456.53..167901758.53 rows=200 width=20)
|
| InitPlan 1 (returns $0)
|
| -> Result (cost=0.00..0.01 rows=1 width=8)
|
| -> GroupAggregate (cost=167901456.52..167901758.52 rows=200 width=12)
|
| Group Key: cle_1.cccccc_id
|
| -> Sort (cost=167901456.52..167901556.52 rows=40000 width=12)
|
| Sort Key: cle_1.cccccc_id
|
| -> HashAggregate (cost=167897998.98..167898398.98
rows=40000 width=12) |
| Group Key: cle_1.cccccc_id, cle_1.aaaaa_id
|
| -> Append (cost=0.00..141132044.98 rows=5353190799
width=12) |
| -> Seq Scan on _hyper_10_71_chunk cle_1
(cost=0.00..35615836.68 rows=1667104768 width=12) |
| -> Seq Scan on _hyper_10_72_chunk cle_2
(cost=0.00..22931461.24 rows=1073370624 width=12) |
| -> Seq Scan on _hyper_10_73_chunk cle_3
(cost=0.00..12029957.12 rows=563086912 width=12) |
| -> Seq Scan on _hyper_10_74_chunk cle_4
(cost=0.00..10949797.28 rows=512535328 width=12) |
| -> Seq Scan on _hyper_10_75_chunk cle_5
(cost=0.00..13747554.76 rows=643480576 width=12) |
| -> Seq Scan on _hyper_10_76_chunk cle_6
(cost=0.00..7800797.52 rows=365127552 width=12) |
| -> Seq Scan on _hyper_10_77_chunk cle_7
(cost=0.00..3433002.24 rows=160687824 width=12) |
| -> Seq Scan on _hyper_10_78_chunk cle_8
(cost=0.00..3689582.96 rows=172702896 width=12) |
| -> Seq Scan on _hyper_10_79_chunk cle_9
(cost=0.00..3913904.52 rows=183197152 width=12) |
| -> Seq Scan on _hyper_10_102_chunk cle_10
(cost=0.00..103.32 rows=4732 width=12) |
| -> Seq Scan on _hyper_10_103_chunk cle_11
(cost=0.00..96.84 rows=4484 width=12) |
| -> Seq Scan on _hyper_10_104_chunk cle_12
(cost=0.00..688.01 rows=32201 width=12) |
| -> Seq Scan on _hyper_10_105_chunk cle_13
(cost=0.00..713.61 rows=33361 width=12) |
| -> Seq Scan on _hyper_10_106_chunk cle_14
(cost=0.00..899.98 rows=42098 width=12) |
| -> Seq Scan on _hyper_10_107_chunk cle_15
(cost=0.00..1264.84 rows=59184 width=12) |
| -> Seq Scan on _hyper_10_108_chunk cle_16
(cost=0.00..2671.00 rows=125000 width=12) |
| -> Seq Scan on _hyper_10_109_chunk cle_17
(cost=0.00..3344.34 rows=156534 width=12) |
| -> Seq Scan on _hyper_10_110_chunk cle_18
(cost=0.00..243609.02 rows=11402202 width=12) |
| -> Seq Scan on _hyper_10_148_chunk cle_19
(cost=0.00..375.74 rows=17574 width=12) |
| -> Seq Scan on _hyper_10_155_chunk cle_20
(cost=0.00..42.39 rows=1939 width=12) |
| -> Seq Scan on _hyper_10_157_chunk cle_21
(cost=0.00..1.46 rows=46 width=12) |
| -> Seq Scan on _hyper_10_158_chunk cle_22
(cost=0.00..46.81 rows=2181 width=12) |
| -> Seq Scan on _hyper_10_159_chunk cle_23
(cost=0.00..5.63 rows=263 width=12) |
| -> Seq Scan on _hyper_10_161_chunk cle_24
(cost=0.00..100.79 rows=4679 width=12) |
| -> Seq Scan on _hyper_10_172_chunk cle_25
(cost=0.00..232.89 rows=10689 width=12) |
| JIT:
|
| Functions: 59
|
| Options: Inlining true, Optimization true, Expressions true, Deforming
true |
+-----------------------------------------------------------------------------------------------------------------------+
EXPLAIN 38
``````text
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
| name | setting
|-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
| BINDIR | /usr/local/pg16/bin
| DOCDIR | /usr/local/pg16/share/doc/postgresql
| HTMLDIR | /usr/local/pg16/share/doc/postgresql
| INCLUDEDIR | /usr/local/pg16/include
| PKGINCLUDEDIR | /usr/local/pg16/include/postgresql
| INCLUDEDIR-SERVER | /usr/local/pg16/include/postgresql/server
| LIBDIR | /usr/local/pg16/lib
| PKGLIBDIR | /usr/local/pg16/lib/postgresql
| LOCALEDIR | /usr/local/pg16/share/locale
| MANDIR | /usr/local/pg16/share/man
| SHAREDIR | /usr/local/pg16/share/postgresql
| SYSCONFDIR | /usr/local/pg16/etc/postgresql
| PGXS | /usr/local/pg16/lib/postgresql/pgxs/src/makefiles/
pgxs.mk
| CONFIGURE | '--prefix=/usr/local/pg16' '--with-openssl'
'-with-lz4' '--with-systemd' '--with-python' '--with-llvm'
| CC | gcc
| CPPFLAGS | -D_GNU_SOURCE
| CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type
-Wshadow=compatible-local -Wfor>
| CFLAGS_SL | -fPIC
| LDFLAGS | -L/usr/lib64 -Wl,--as-needed
-Wl,-rpath,'/usr/local/pg16/lib',--enable-new-dtags
| LDFLAGS_EX |
| LDFLAGS_SL |
| LIBS | -lpgcommon -lpgport -llz4 -lssl -lcrypto -lz -ledit
-lm
| VERSION | PostgreSQL 16.2
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
```