PostgreSQL数据库异步（并发）查询为何产生延迟？

我有一个PostgreSQL数据库，对同一张表执行多次异步（并发）查询时，每个查询的耗时比单次查询或同步（顺序）查询更长。已经试过两种连接方式：通过REST API连接EJB服务器，以及直接用psql连接数据库。

测试场景与结果 #

针对同一查询执行10次，使用的命令：
docker exec -i container-name psql -Uusername -d databasename -c "..."

同步（顺序）执行结果 #

Finished 01 in 0h 0m 5s 134 ms.
Finished 02 in 0h 0m 5s 139 ms.
Finished 03 in 0h 0m 5s 181 ms.
Finished 04 in 0h 0m 5s 270 ms.
Finished 05 in 0h 0m 5s 157 ms.
Finished 06 in 0h 0m 5s 163 ms.
Finished 07 in 0h 0m 5s 178 ms.
Finished 08 in 0h 0m 5s 205 ms.
Finished 09 in 0h 0m 5s 169 ms.
Finished 10 in 0h 0m 5s 155 ms.

异步（并发）执行结果 #

Finished 03 in 0h 0m 12s 634 ms.
Finished 04 in 0h 0m 18s 87 ms.
Finished 07 in 0h 0m 21s 209 ms.
Finished 01 in 0h 0m 21s 279 ms.
Finished 05 in 0h 0m 21s 302 ms.
Finished 02 in 0h 0m 21s 659 ms.
Finished 09 in 0h 0m 22s 127 ms.
Finished 06 in 0h 0m 22s 394 ms.
Finished 08 in 0h 0m 22s 667 ms.
Finished 10 in 0h 0m 22s 671 ms.

疑问与补充信息 #

• 了解到PostgreSQL不支持单连接下挂起/恢复事务，并发查询需要每个查询对应一个连接，但不确定后续该如何调整。
• 异步查询时查看pg_stat_activity表，每条查询对应一行，但不确定这是多连接还是单连接下的多查询。
• 用于测试的脚本如下，移除&后会顺序执行函数调用：

#!/usr/bin/env bash

set -e

function runScript1() {
    _START_T=$(date +%s%N)
    docker exec -i container-name psql -Uusername -d databse -c "SELECT ... FROM ..."
    _END_T=$(date +%s%N)
    _ELAPSED_MS=$(((_END_T - _START_T) / 1000000))
    _SECONDS=$(( _ELAPSED_MS / 1000 ))
    _MILLISECONDS=$(( _ELAPSED_MS % 1000 ))
    _MINUTES=$(( _SECONDS / 60 ))
    _HOURS=$(( _MINUTES / 60 ))

    # Print the result
    echo "Finished $1 in ${_HOURS}h $((_MINUTES % 60))m $((_SECONDS % 60))s $_MILLISECONDS ms."
}

_START_TOTAL=$(date +%s%N)
runScript1 "01" &
runScript1 "02" &
runScript1 "03" &
runScript1 "04" &
runScript1 "05" &
runScript1 "06" &
runScript1 "07" &
runScript1 "08" &
runScript1 "09" &
runScript1 "10" &
_END_TOTAL=$(date +%s%N)

内容的提问来源于stack exchange，提问作者Marianna