- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我已经大大简化了示例,希望能产生一个足够清晰且可以回答的问题:
考虑一个事件表
CREATE TABLE alertable_events
(
unique_id text NOT NULL DEFAULT ''::text,
generated_on timestamp without time zone NOT NULL DEFAULT now(),
message_text text NOT NULL DEFAULT ''::text,
CONSTRAINT pk_alertable_events PRIMARY KEY (unique_id),
)
具有以下数据:
COPY alertable_events (unique_id,message_text,generated_on) FROM stdin;
one message one 2014-03-20 06:00:00.000000
two message two 2014-03-21 06:00:00.000000
three message three 2014-03-22 06:00:00.000000
four message four 2014-03-23 06:00:00.000000
five message five 2014-03-24 06:00:00.000000
\.
对于每个事件,都有一个字段列表
CREATE TABLE alertable_event_fields
(
unique_id text NOT NULL DEFAULT ''::text,
field_name text NOT NULL,
field_value text NOT NULL DEFAULT ''::text,
CONSTRAINT pk_alertable_event_fields PRIMARY KEY (unique_id, field_name),
CONSTRAINT fk_alertable_event_fields_0 FOREIGN KEY (unique_id)
REFERENCES alertable_events (unique_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
)
具有以下数据:
COPY alertable_event_fields (unique_id,field_name,field_value) FROM stdin;
one field1 a
one field2 b
two field1 z
two field2 y
three field1 a
three field2 m
four field1 a
four field2 b
five field1 z
five field2 y
\.
我想定义一个产生以下内容的 View :
| unique_id | fields | message_text | generated_on | updated_on | count |
| five | z|y | message five | 2014-03-21 06:00:00.000000 | 2014-03-24 06:00:00.000000 | 2 |
| four | a|b | message four | 2014-03-20 06:00:00.000000 | 2014-03-23 06:00:00.000000 | 2 |
| three | a|m | message three | 2014-03-22 06:00:00.000000 | 2014-03-22 06:00:00.000000 | 1 |
值得注意的是:
a|b
将不匹配 b|a
我已经生成了这个 View ,它适用于小型数据集,但是,随着 alertable_events 表的增长,它会变得异常缓慢。我只能假设我在 View 中做错了什么,因为我从来没有处理过如此丑陋的事情。
美国东部时间 3 月 30 日下午 12:15 更新 看起来我可能有服务器调整问题导致运行时间过长,请参阅添加的 explain
了解更多信息。如果您在那里看到明显的问题,我将非常有兴趣调整服务器的配置。
任何人都可以拼凑出一个能够很好地处理大型数据集并且运行时间明显比这更好的 View 吗?也许使用 hstore? (我最好运行 9.2,但如果我可以对字段进行良好的 json 编码,则运行 9.3。)
更新于 3 月 30 日上午 11:30 我开始认为我的问题可能是服务器调整(这意味着我需要与 SA 交谈)这是一个非常简单的解释(analyze,buffers)
这显示了 unduplicated_event_fields 中少至 8k 行的可笑运行时间
3 月 30 日晚上 7:20 更新 我使用 SET WORK_MEM='5MB'
将可用内存增加到 5MB(对于下面的查询来说足够了),奇怪的是,即使规划器在内存中进行快速排序,它实际上平均要多花 100 毫秒!
explain (analyze,buffers)
SELECT a.unique_id,
array_to_string(array_agg(a.field_value order by a.field_name),'|') AS "values"
FROM alertable_event_fields a
GROUP BY a.unique_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=771.11..892.79 rows=4056 width=80) (actual time=588.679..630.989 rows=4056 loops=1)
Buffers: shared hit=143, temp read=90 written=90
-> Sort (cost=771.11..791.39 rows=8112 width=80) (actual time=588.591..592.622 rows=8112 loops=1)
Sort Key: unique_id
Sort Method: external merge Disk: 712kB
Buffers: shared hit=143, temp read=90 written=90
-> Seq Scan on alertable_event_fields a (cost=0.00..244.40 rows=8112 width=80) (actual time=0.018..5.478 rows=8112 loops=1)
Filter: (message_name = 'LIMIT_STATUS'::text)
Buffers: shared hit=143
Total runtime: 632.323 ms
(10 rows)
更新 3/30 4:10AM EDT 我仍然不完全满意,并且对任何进一步的优化感兴趣。我有一个支持 500msgs/sec 稳定状态的要求,虽然其中大部分不应该是“事件”,但我现在在压力测试时遇到了一些积压。
更新 3/30 12:00PM EDT 这是我迄今为止最可读的迭代,不幸的是,对于 4000 行,我仍在寻找 600 毫秒的运行时间! ...(见上文,因为它主要包含在最内层的查询中)这里的任何帮助将不胜感激
CREATE OR REPLACE VIEW views.unduplicated_events AS
SELECT a.unique_id,a.message_text,
b."values",b.generated_on,b.updated_on,b.count
FROM alertable_events a
JOIN (
SELECT b."values",
min(a.generated_on) AS generated_on,
max(a.generated_on) AS updated_on,
count(*) AS count
FROM alertable_events a
JOIN (
SELECT a.unique_id,
array_to_string(array_agg(a.field_value order by a.field_name),'|') AS "values"
FROM alertable_event_fields a
GROUP BY a.unique_id
) b USING (unique_id)
GROUP BY b."values"
) b ON a.generated_on=b.updated_on
ORDER BY updated_on DESC;
美国东部时间 3 月 30 日中午 12:00 更新删除了旧内容,因为时间太长了
最佳答案
一些建议
除非 generated_on
是唯一的,否则您当前的查询是不正确的,这在问题中未声明并且可能并非如此:
CREATE OR REPLACE VIEW views.unduplicated_events AS
SELECT ...
FROM alertable_events a
JOIN ( ... ) b ON a.generated_on=b.updated_on -- !! unreliable
SELECT DISTINCT ON (f.fields)
unique_id -- most recent
, f.fields
, e.message_text -- most recent
, min(e.generated_on) OVER (PARTITION BY f.fields) AS generated_on -- "first"
, e.generated_on AS updated_on -- most recent
, count(*) OVER (PARTITION BY f.fields) AS ct
FROM alertable_events e
JOIN (
SELECT unique_id, array_to_string(array_agg(field_value), '|') AS fields
FROM (
SELECT unique_id, field_value
FROM alertable_event_fields
ORDER BY 1, field_name -- a bit of a hack, but much faster
) f
GROUP BY 1
) f USING (unique_id)
ORDER BY f.fields, e.generated_on DESC;
结果当前按字段
排序。如果您需要不同的排序顺序,则需要将其包装在另一个子查询中 ...
输出列名称 generated_on
与输入列 generated_on
冲突。您必须对列 e.generated_on
进行表限定以引用输入列。我在各处都添加了表限定以使其清楚,但实际上只需要 ORDER BY
子句。 The manual:
If an
ORDER BY
expression is a simple name that matches both anoutput column name and an input column name,ORDER BY
will interpretit as the output column name. This is the opposite of the choice thatGROUP BY
will make in the same situation. This inconsistency is madeto be compatible with the SQL standard.
更新后的查询也应该更快(一如既往的预期)。再次运行 EXPLAIN ANALYZE
。
对于整个查询,索引几乎没有用处。仅当您选择特定行时...一个可能的异常(exception):alertable_event_fields
的覆盖索引:
CREATE INDEX f_idx1
ON alertable_event_fields (unique_id, field_name, field_value);
array_agg(field_value ORDER BY ...)
对于大集合来说往往比子查询中的预排序慢。
DISTINCT ON
在这里很方便。但不确定它是否真的更快,因为 ct
和 generated_on
必须在单独的窗口函数中计算,这需要另一个排序步骤。
work_mem
:将其设置太高实际上会损害性能。 More in the Postgres Wiki.或在 "Craig's list" .
通常这难以优化。索引失败是因为排序顺序取决于两个表。如果您可以使用快照,请考虑 MATERIALIZED VIEW
.
关于postgresql - 优化 postgres View 的时间戳和来自另一个表的字段聚合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22738300/
我的 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 中是否有一种简单的方法可以做到这一点?
我是一名优秀的程序员,十分优秀!