- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在运行的查询随着记录的添加而逐渐变慢。 通过自动化过程(bash 调用 psql)不断添加记录。我想纠正这个瓶颈;但是,我不知道我最好的选择是什么。
Hour Count Duration Avg duration
00 9,990 10m3s 60ms <---ignore this hour
02 1 60ms 60ms <---ignore this hour
03 4,638 1m54s 24ms <---queries begin with table empty
04 30,991 55m49s 108ms <---first full hour of queries running
05 13,497 58m3s 258ms
06 9,904 58m32s 354ms
07 10,542 58m25s 332ms
08 8,599 58m42s 409ms
09 7,360 58m52s 479ms
10 6,661 58m57s 531ms
11 6,133 59m2s 577ms
12 5,601 59m6s 633ms
13 5,327 59m9s 666ms
14 4,964 59m12s 715ms
15 4,759 59m14s 746ms
16 4,531 59m17s 785ms
17 4,330 59m18s 821ms
18 939 13m16s 848ms
表结构如下:
CREATE TABLE "Parent" (
"ParentID" SERIAL PRIMARY KEY,
"Details1" VARCHAR
);
表 "Parent"
与表 "Foo"
具有一对多关系:
CREATE TABLE "Foo" (
"FooID" SERIAL PRIMARY KEY,
"ParentID" int4 NOT NULL REFERENCES "Parent" ("ParentID"),
"Details1" VARCHAR
);
表 "Foo"
与表 "Bar"
具有一对多关系:
CREATE TABLE "Bar" (
"FooID" int8 NOT NULL REFERENCES "Foo" ("FooID"),
"Timerange" tstzrange NOT NULL,
"Detail1" VARCHAR,
"Detail2" VARCHAR,
CONSTRAINT "Bar_pkey" PRIMARY KEY ("FooID", "Timerange")
);
CREATE INDEX "Bar_FooID_Timerange_idx" ON "Bar" USING gist("FooID", "Timerange");
此外,表 “Bar”
不能包含相同 “FooID”
或 的重叠
。 我创建了一个触发器,它在任何 “Timespan”
值>“ParentID”INSERT
、UPDATE
或 DELETE
之后触发防止重叠范围。
触发器包含一个部分,看起来类似:
WITH
"cte" AS (
SELECT
"Foo"."FooID",
"Foo"."ParentID",
"Foo"."Details1",
"Bar"."Timespan"
FROM
"Foo"
JOIN "Bar" ON "Foo"."FooID" = "Bar"."FooID"
WHERE
"Foo"."FooID" = 1234
)
SELECT
"Foo"."FooID",
"Foo"."ParentID",
"Foo"."Details1",
"Bar"."Timespan"
FROM
"cte"
JOIN "Foo" ON
"cte"."ParentID" = "Foo"."ParentID"
AND "cte"."FooID" <> "Foo"."FooID"
JOIN "Bar" ON
"Foo"."FooID" = "Bar"."FooID"
AND "cte"."Timespan" && "Bar"."Timespan";
EXPLAIN ANALYSE
的结果:
Nested Loop (cost=7258.08..15540.26 rows=1 width=130) (actual time=8.052..147.792 rows=1 loops=1)
Join Filter: ((cte."FooID" <> "Foo"."FooID") AND (cte."ParentID" = "Foo"."ParentID"))
Rows Removed by Join Filter: 76
CTE cte
-> Nested Loop (cost=0.68..7257.25 rows=1000 width=160) (actual time=1.727..1.735 rows=1 loops=1)
-> Function Scan on "fn_Bar" (cost=0.25..10.25 rows=1000 width=104) (actual time=1.699..1.701 rows=1 loops=1)
-> Index Scan using "Foo_pkey" on "Foo" "Foo_1" (cost=0.42..7.24 rows=1 width=64) (actual time=0.023..0.025 rows=1 loops=1)
Index Cond: ("FooID" = "fn_Bar"."FooID")
-> Nested Loop (cost=0.41..8256.00 rows=50 width=86) (actual time=1.828..147.188 rows=77 loops=1)
-> CTE Scan on cte (cost=0.00..20.00 rows=1000 width=108) (actual time=1.730..1.740 rows=1 loops=1)
**** -> Index Scan using "Bar_FooID_Timerange_idx" on "Bar" (cost=0.41..8.23 rows=1 width=74) (actual time=0.093..145.314 rows=77 loops=1)
Index Cond: ((cte."Timespan" && "Timespan"))
-> Index Scan using "Foo_pkey" on "Foo" (cost=0.42..0.53 rows=1 width=64) (actual time=0.004..0.005 rows=1 loops=77)
Index Cond: ("FooID" = "Bar"."FooID")
Planning time: 1.490 ms
Execution time: 147.869 ms
(**** 强调我的)
这似乎表明 99% 的工作是在从 "cte"
到 "Bar"
的 JOIN
中完成的(通过"Foo"
) ...但它已经在使用适当的索引...它仍然太慢了。
所以我跑了:
SELECT
pg_size_pretty(pg_relation_size('"Bar"')) AS "Table",
pg_size_pretty(pg_relation_size('"Bar_FooID_Timerange_idx"')) AS "Index";
结果:
Table | Index
-------------|-------------
283 MB | 90 MB
这种大小(相对于表)的索引在读取性能方面是否提供很多?我正在考虑一个 sudo 分区,其中索引被几个部分索引替换......也许部分需要更少的维护(和读取)并且性能会提高。我从未见过这样做,只是一个想法。如果这是一个选项,我想不出任何好的方法来限制段,因为这将在 TSTZRANGE
值上。
我还认为将 "ParentID"
添加到 "Bar"
会加快处理速度,但我不想反规范化。
我还有什么选择?
在性能高峰期(18:00 点),该过程一直每秒添加 14.5 条记录...从每秒 1.15 条记录增加。
这是结果:
"ParentID"
添加到表 "Bar"
"Foo"("ParentID", "FooID")
添加外键约束EXCLUDE USING gist ("ParentID"WITH =, "Timerange"WITH &&) DEFERRABLE INITIALLY DEFERRED
(btree_gist 模块已安装)最佳答案
Additionally, table
"Bar"
may not contain overlapping"Timespan"
values for the same"FooID"
or"ParentID"
. I have created a trigger that fires after anyINSERT
,UPDATE
, orDELETE
that prevents overlapping ranges.
我建议你改用排除约束,这样更简单、更安全、更快:
您需要安装附加模块btree_gist
第一的。请参阅此相关答案中的说明和解释:
并且您需要在表“Bar”
中冗余地包含"ParentID"
,这是一个很小的代价。表定义可能如下所示:
CREATE TABLE "Foo" (
"FooID" serial PRIMARY KEY
"ParentID" int4 NOT NULL REFERENCES "Parent"
"Details1" varchar
<b>CONSTRAINT foo_parent_foo_uni UNIQUE ("ParentID", "FooID")</b> -- required for FK
);
CREATE TABLE "Bar" (
"ParentID" int4 NOT NULL,
"FooID" <b>int4</b> NOT NULL REFERENCES "Foo" ("FooID"),
"Timerange" tstzrange NOT NULL,
"Detail1" varchar,
"Detail2" varchar,
CONSTRAINT "Bar_pkey" PRIMARY KEY ("FooID", "Timerange"),
<b>CONSTRAINT bar_foo_fk
FOREIGN KEY ("ParentID", "FooID") REFERENCES "Foo" ("ParentID", "FooID"),
CONSTRAINT bar_parent_timerange_excl
EXCLUDE USING gist ("ParentID" WITH =, "Timerange" WITH &&)</b>
);
我还将 "Bar"."FooID"
的数据类型从
更改为 int8
int4
。它引用 "Foo"."FooID"
,这是一个 serial
,即 int4
。使用匹配类型 int4
(或只是 integer
)有几个原因,其中之一是性能。
您不再需要触发器(至少对于此任务不需要),并且您不再创建索引
,因为它是由排除约束隐式创建的。"Bar_FooID_Timerange_idx"
("ParentID", "FooID")
上的 btree 索引很可能有用,不过:
CREATE INDEX bar_parentid_fooid_idx ON "Bar" ("ParentID", "FooID");
相关:
我选择 UNIQUE ("ParentID", "FooID")
而不是反过来是有原因的,因为还有另一个以 "FooID"
开头的索引任一表:
旁白:I never use double-quoted CaMeL-case identifiers在 Postgres 中。我这样做只是为了符合您的布局。
如果您不能或不会冗余地包含 "Bar"."ParentID"
,还有另一种流氓方式 - 条件是 "Foo"。 “ParentID”
从未更新。确保这一点,例如使用触发器。
你可以伪造一个IMMUTABLE
函数:
CREATE OR REPLACE FUNCTION f_parent_of_foo(int)
RETURNS int AS
'SELECT "ParentID" FROM public."Foo" WHERE "FooID" = $1'
LANGUAGE sql IMMUTABLE;
我假设 public
对表名进行模式限定以确保。适应您的模式。
更多:
然后在排除约束中使用它:
CONSTRAINT bar_parent_timerange_excl
EXCLUDE USING gist (<b>f_parent_of_foo("FooID")</b> WITH =, "Timerange" WITH &&)
虽然节省了一个冗余的 int4
列,但约束的验证成本更高,整个解决方案取决于更多的前提条件。
您可以将 INSERT
和 UPDATE
包装到一个 plpgsql 函数中,并从排除约束 (23P01 exclusion_violation
) 中捕获可能的异常以对其进行一些处理方式。
INSERT ...
EXCEPTION
WHEN exclusion_violation
THEN -- handle conflict
完整代码示例:
在 Postgres 9.5 中,您可以使用新的“UPSERT”实现直接处理 INSERT
。 The documentation:
The optional
ON CONFLICT
clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified byconflict_target
is violated, the alternativeconflict_action
is taken.ON CONFLICT DO NOTHING
simply avoids inserting a row as its alternative action.ON CONFLICT DO UPDATE
updates the existing row that conflicts with the row proposed for insertion as its alternative action.
但是:
Note that exclusion constraints are not supported with
ON CONFLICT DO UPDATE
.
但是您仍然可以使用ON CONFLICT DO NOTHING
,从而避免可能的exclusion_violation
异常。只需检查是否实际更新了任何行,这样成本更低:
INSERT ...
ON CONFLICT ON CONSTRAINT bar_parent_timerange_excl DO NOTHING;
IF NOT FOUND THEN
-- handle conflict
END IF;
此示例将检查限制为给定的排除约束。 (我在上面的表定义中为此明确命名了约束。)其他可能的异常未被捕获。
关于sql - 使用 && 加入 TSTZRANGE 时,Postgresql 9.4 查询变得越来越慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33987739/
我的 postgresql 有问题,我复制了所有文件,然后将其删除。然后,我安装了新的,问题就解决了。现在可以将旧文件和文件导入新文件吗? 最佳答案 如果它们是相同的主要版本(即 9.0 到 9.0.
我想使用 Postgresql 9.2.2 来存储我的应用程序的数据。我不得不构建一个应该基于数据库级别的触发器(当数据库启动时,这个触发器将被触发并执行。),当 postgresql 服务器启动时是
我已经使用下面的查询从 Postgresql 目录表中获取 Sequence 对象的完整信息 select s.sequence_name, s.start_value, s.minimum_valu
Postgres 版本:9.3.4 我需要执行驻留在远程数据库中的函数。该函数根据给定的参数返回一个统计数据表。 我实际上只是在我的本地数据库中镜像该函数,以使用我的数据库角色和授权来锁定对该函数的访
我在 CentOS 7 上,我正在尝试解决“PG::ConnectionBad: FATAL: Peer authentication failed for user”错误。 所以我已经想出我应该更改
我写了一个触发器函数,在触发器表列名上循环,我从具有不同列的不同表调用该函数。该函数将列名插入到数组中并在它们上循环,以便将值插入到另一个模式和表中。 函数和触发器创建脚本: DROP TRIGGER
PostgreSQL 的默认空闲连接超时是多少,我运行了 show idle_in_transaction_session_timeout 查询并返回了 0,但是值 0 表示此选项被禁用,但我想知道默
我需要将十六进制值存储到数据库表中,谁能推荐我需要用于属性的数据类型? 提前致谢 最佳答案 您可以使用bytea 来存储十六进制格式。更多信息 can be found in the postgres
我有一个具有复合主键的(大)表,由 5 列(a、b、c、d、e)组成。 我想高效地选择具有其中两列 (a + e) 的所有行到给定值。 在 PostgreSQL 中,我需要索引吗?或者数据库会使用主键
在阅读 PostreSQL (13) 文档时,我遇到了 this页面,其中列出了不同日期时间类型的存储大小。 除其他外,它指出: Name Storag
我有两个大整数的巨大表(500 000 000 行)。两列都被单独索引。我正在使用语法批量插入此表: INSERT into table (col1, col2) VALUES(x0, y0), (x
有一台 CentOS7 Linux 机器正在运行(不是由我管理;拥有有限的权限)。 请求在其中设置 PostgreSQL。 刚刚从 CentOS 存储库安装了 PostgreSQL: sudo yum
我在 Ubuntu 18.04 上安装了 Postgresql 10,但不知何故坏了,不会重新启动。我可以重新安装它而不破坏它的数据库,以便我可以再次访问数据库吗? pg_dump 不起作用。 最佳答
我想在 UNIX 中使用 crontab 自动备份 PostgreSQL 数据库。我已经尝试过,但它会创建 0 字节备份。 我的 crontab 条目是: 24 * * * * /home/desk
我已经完成了PG服务器的安装。我希望能够使用 pgAdmin 远程连接到它,但不断收到服务器不听错误。 could not connect to server: Connection refused
Oracle 支持波斯历但需要知道 PostgreSQL 是否支持波斯历? 如果是,那么我们如何在 PostgreSQL 中将默认日历类型设置为 Persian 而不是 Gregorian(在 Ora
假设我们有一个带有表的 SQL 数据库 Person以及访问它的几个应用程序。出于某种原因,我们想修改 Person表以向后不兼容的方式。 保持兼容性的一种潜在解决方案是将表重命名为 User并创建一
我使用 PostgreSQL 中的模式来组织我庞大的会计数据库。每年年底,我都会通过为下一年创建一个新模式来进行协调过程。 新模式的文件是否与旧模式物理分离?或者所有模式一起存储在硬盘上? 这对我来说
我正在尝试使用配置文件中的以下配置参数调整 PostgreSQL 服务器: autovacuum_freeze_max_age = 500000000 autovacuum_max_workers =
我的数据包含数据库列中的表情符号,即 message_text ------- 🙂 😀 Hi 😀 我只想查询包含表情符号的数据的行。在 postgres 中是否有一种简单的方法可以做到这一点?
我是一名优秀的程序员,十分优秀!