我使用单个用户运行 postgres 16.1,服务器上没有其他活动。我多次运行这个测试,结果非常一致。
问题
下面的查询将以非常一致的计时和相同的执行计划运行(与服务器是否在 3 秒前启动或运行数周无关)。但总执行时间取决于查询是从 SQL 客户端还是从服务器上的 psql 运行。
任何 SQL 客户端的时间大约是 psql 的 3 倍。但是如果我在 SQL 客户端中运行相同的查询但使用
解释分析 wal 缓冲区的详细计时
时间与 psql 执行非常相似。
我观察到,psql 的执行使用了 5 个内核,对应于 ax_parallel_workers_per_gather=5 设置。另一方面,SQL-Client 的执行仅使用 1 个核心,执行时间约为 90 秒,而不是 30 秒。
你能帮我详细说明一下吗?
2024-02-01 09:56:25.828 CET [650041] postgres@E_LOG: duration: 29491.907 ms plan:
Query Text: WITH
"x0" AS
(
SELECT
"x1"."cal_dateid" AS "cal_dateid",
"x1"."cal_dateint" AS "cal_dateint",
"x1"."cal_date" AS "cal_date",
"x1"."cal_year" AS "cal_year",
"x1"."cal_month" AS "cal_month",
"x1"."cal_day" AS "cal_day",
"x1"."cal_yearmonth" AS "cal_yearmonth",
"x1"."cal_datetext" AS "cal_datetext",
"x1"."cal_yearmonthtext" AS "cal_yearmonthtext",
"x1"."cal_monthtext" AS "cal_monthtext",
extract(week from "x1"."cal_date") AS "c_date",
"x1"."QWE" AS "QWE"
FROM
"public"."ABC" "x1"
),
"DEF" AS
(
SELECT
"x0"."cal_date" AS "cal_date",
"x0"."cal_year" AS "cal_year"
FROM
"x0"
)
SELECT
"DEF"."cal_year" AS "intr_year",
SUM("ABC_ALL"."ID") AS "ID",
COUNT(DISTINCT "ABC_ALL"."id") AS "id_sing",
SUM("CDC"."SUM") AS "SUM_1"
FROM
"public"."ABC_2" "ABC_ALL"
LEFT OUTER JOIN "DEF"
ON "DEF"."cal_date" = "ABC_ALL"."date_occ"
LEFT OUTER JOIN "public"."case" "CDC"
ON "CDC"."id_fk" = "ABC_ALL"."occ_id"
GROUP BY
"DEF"."cal_year"
ORDER BY
"intr_year" DESC NULLS last;
GroupAggregate (cost=1753092.95..5239798.05 rows=151 width=28)
Group Key: x1.cal_year
-> Gather Merge (cost=1753092.95..4973483.11 rows=26631343 width=26)
Workers Planned: 5
-> Sort (cost=1752092.87..1765408.54 rows=5326269 width=26)
Sort Key: x1.cal_year DESC NULLS LAST, ABC_ALL.id
-> Hash Left Join (cost=164169.92..1084201.62 rows=5326269 width=26)
Hash Cond: (ABC_ALL.date_occ = x1.cal_date)
-> Parallel Hash Right Join (cost=162083.00..1068132.16 rows=5326269 width=26)
Hash Cond: (CDC.id_fk = ABC_ALL.occ_id)
-> Parallel Seq Scan on case CDC (cost=0.00..892067.69 rows=5326269 width=12)
-> Parallel Hash (cost=155333.00..155333.00 rows=540000 width=22)
-> Parallel Seq Scan on ABC_2 ABC_ALL (cost=0.00..155333.00 rows=540000 width=22)
-> Hash (cost=1397.52..1397.52 rows=55152 width=8)
-> Seq Scan on ABC x1 (cost=0.00..1397.52 rows=55152 width=8)
JIT:
Functions: 22
Options: Inlining true, Optimization true, Expressions true, Deforming true