作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我们有一个查询来获取在特定时间段内发生变化的所有工作。根据选定的时间段,性能从一天的 <100 毫秒到一周的约 7 秒。
我发现如果时间段足够小,使用索引,查询速度很快。如果周期过大,则不使用索引,查询变慢。
服务器运行版本 9.2
.
为什么会出现这种情况以及如何解决此问题?
创建脚本:
CREATE TABLE IF NOT EXISTS "Job"
(
"id" serial PRIMARY KEY,
"serial" TEXT NOT NULL
);
CREATE UNIQUE INDEX "index_Job_serial" ON "Job" ("serial" ASC);
CREATE TABLE IF NOT EXISTS "Property"
(
"id" serial PRIMARY KEY,
"name" TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "Timestamp"
(
"id" serial PRIMARY KEY,
"usSince1970" BIGINT NOT NULL ,
"localTime" TEXT
);
CREATE INDEX "index_Timestamp_usSince1970" ON "Timestamp" USING btree ("usSince1970");
CREATE TABLE IF NOT EXISTS "Changes"
(
"idJob" INTEGER NOT NULL ,
"idProperty" INTEGER NOT NULL ,
"idTimestamp" INTEGER NOT NULL ,
"value1" decimal(25,5),
"value2" INTEGER ,
"value3" TEXT ,
PRIMARY KEY ("idJob", "idProperty", "idTimestamp") ,
FOREIGN KEY ("idJob" ) REFERENCES "Job" ("id" ) ,
FOREIGN KEY ("idProperty" ) REFERENCES "Property" ("id" ) ,
FOREIGN KEY ("idTimestamp" ) REFERENCES "Timestamp" ("id" )
);
CREATE INDEX "index_Changes_idJob" ON "Changes" ("idJob" ASC);
CREATE INDEX "index_Changes_idProperty" ON "Changes" ("idProperty" ASC);
CREATE INDEX "index_Changes_idTimestamp" ON "Changes" ("idTimestamp" DESC);
-- fast query (1 day)
SELECT DISTINCT "idJob"
FROM "Changes"
INNER JOIN "Timestamp" ON "Timestamp"."id" = "Changes"."idTimestamp"
WHERE "Timestamp"."usSince1970" between 1584831600000000 and 1584745200000000
-- explain
HashAggregate (cost=26383.48..26444.33 rows=6085 width=4) (actual time=8.039..8.078 rows=179 loops=1)
-> Nested Loop (cost=0.00..26368.26 rows=6085 width=4) (actual time=0.031..7.059 rows=6498 loops=1)
-> Index Scan using "index_Timestamp_usSince1970" on "Timestamp" (cost=0.00..96.25 rows=2510 width=4) (actual time=0.022..0.514 rows=2671 loops=1)
Index Cond: (("usSince1970" >= 1584745200000000::bigint) AND ("usSince1970" <= 1584831600000000::bigint))
-> Index Scan using "index_Changes_idTimestamp" on "Changes" (cost=0.00..10.27 rows=20 width=8) (actual time=0.002..0.002 rows=2 loops=2671)
Index Cond: ("idTimestamp" = "Timestamp".id)
Total runtime: 8.204 ms
-- slow query (7 days)
SELECT distinct "idJob"
FROM "Changes"
INNER JOIN "Timestamp" ON "Timestamp"."id" = "Changes"."idTimestamp"
WHERE "Timestamp"."usSince1970" between 1583708400000000 and 1584313200000000
-- explain
Unique (cost=570694.82..571824.16 rows=92521 width=4) (actual time=8869.569..8930.545 rows=3695 loops=1)
-> Sort (cost=570694.82..571259.49 rows=225867 width=4) (actual time=8869.568..8915.372 rows=260705 loops=1)
Sort Key: "Changes"."idJob"
Sort Method: external merge Disk: 3552kB
-> Hash Join (cost=4926.44..547518.97 rows=225867 width=4) (actual time=6325.494..8734.353 rows=260705 loops=1)
Hash Cond: ("Changes"."idTimestamp" = "Timestamp".id)
-> Seq Scan on "Changes" (cost=0.00..250722.43 rows=16238343 width=8) (actual time=0.004..2505.794 rows=16238343 loops=1)
-> Hash (cost=3397.79..3397.79 rows=93172 width=4) (actual time=42.392..42.392 rows=107093 loops=1)
Buckets: 4096 Batches: 4 Memory Usage: 948kB
-> Index Scan using "index_Timestamp_usSince1970" on "Timestamp" (cost=0.00..3397.79 rows=93172 width=4) (actual time=0.006..20.831 rows=107093 loops=1)
Index Cond: (("usSince1970" >= 1583708400000000::bigint) AND ("usSince1970" <= 1584313200000000::bigint))
Total runtime: 8932.374 ms
最佳答案
慢查询处理更多的数据(来自 "Timestamp"
的 100000 行 vs. 2500 行),所以它变慢也就不足为奇了。
您也可以强制 PostgreSQL 对慢查询使用嵌套循环连接:
BEGIN;
SET LOCAL enable_hashjoin = off;
SET LOCAL enable_mergejoin = off;
SELECT ...;
COMMIT;
work_mem
.
VACUUM
"Changes"
通常情况下,您可以通过仅索引扫描获得更好的性能:
CREATE INDEX ON "Changes" ("idTimestamp") INCLUDE ("idJob");
CREATE INDEX ON "Changes" ("idTimestamp", "idJob");
"index_Changes_idTimestamp"
.
关于sql - PostgreSQL:未使用索引导致查询性能不佳?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/61099692/
我是一名优秀的程序员,十分优秀!