正文 

当前位置： 首页 > 知识 >

如何在 Postgres 中优化此查询

     2023-04-14     246

关键词：

【中文标题】如何在 Postgres 中优化此查询【英文标题】：How can I optimize this query in Postgres 【发布时间】：2021-02-12 14:19:23 【问题描述】：

以下查询需要更多时间来运行。如何优化以下查询以运行更多记录？我已经为这个查询运行了Explain Analyze。附上相同的输出。

这是作为视图创建的现有查询，需要很长时间（超过几个小时）才能返回结果。

我已经对这两张表进行了清理、分析和重新索引，但没有运气。

select st_tr.step_trail_id,
       st_tr.test_id,
       st_tr.trail_id,
       st_tr.step_name, 
       filter.regular_expression as filter_expression,
       filter.order_of_occurrence as filter_order,
       filter.match_type as filter_match_type,
       null as begins_with,
       null as ends_with,
       null as input_source,
       null as pattern_expression,
       null as pattern_matched,
       null as pattern_status,
       null as pattern_order,
       'filter' as record_type
from tab_report_step st_tr, 
     tab_report_filter filter
where st_tr.st_tr_id = filter.st_tr_id)

查询计划：

 Hash Join  (cost=446852.58..1176380.76 rows=6353676 width=489) (actual time=16641.953..47270.831 rows=6345360 loops=1)
   Buffers: shared hit=1 read=451605 dirtied=5456 written=5424, temp read=154080 written=154074
   -> Seq Scan on tab_report_filter filter  (cost=0..24482.76 rows=6353676 width=161) (actual time=0.041..8097.233 rows=6345360 loops=1)
      Buffers: shared read=179946 dirtied=4531 written=4499
   -> Hash  (cost=318817.7..318817.7 rows=4716070 width=89) (actual time=16627.291..16627.291 rows=4709040 loops=1)
      Buffers: shared hit=1 read=271656 dirtied=925 written=925, temp written=47629
        -> Seq Scan on tab_report_step st_tr  (cost=0..318817.7 rows=4716070 width=89) (actual time=0.059..10215.484 rows=4709040 loops=1)
           Buffers: shared hit=1 read=271656 dirtied=925 written=925

【问题讨论】：

这是一个表中的 470 万条记录和另一个表中的 640 万条记录之间的连接，您想要它们全部。它应该有多快？它每毫秒大约有 130 条记录，不是那么好，但也不是那么糟糕。你对 work_mem 的设置是什么？ 感谢您的回复。 work_mem 设置为 200MB。 47 秒肯定不是“小时” 如果我在 st_tr.st_tr_id = filter.st_tr_id 上使用 join，会提高性能吗？或者有没有更好的方法来优化这个查询？ 【参考方案1】：

您没有在这些表上运行VACUUM。也许是VACUUM (FULL)，但肯定不是VACUUM。

有两点可以改进：

确保在您阅读页面时不要弄脏或写入页面。这很可能是因为这是您第一次读取行，而 PostgreSQL 设置了 提示位。

运行VACUUM（没有FULL）可以解决这个问题。另外，如果你重复这个实验，你不应该再得到那些脏的和写入的缓冲区了。

通过增加work_mem 为查询提供更多内存。哈希不适合work_mem 并溢出到磁盘，这会导致额外的磁盘读取和写入，这对性能不利。

由于您连接两个没有限制WHERE 条件的大表并且有很多结果行，因此此查询永远不会很快。

【讨论】：

感谢您的回复。是的，我在两张桌子上都运行了 VACUUM FULL。将尝试使用 VACUUM。 work_mem 为 200 MB。

如何在 MySQL 中优化此查询

】如何在MySQL中优化此查询【英文标题】：HowtooptimizethisqueryinMySQL【发布时间】：2015-10-2718:08:21【问题描述】：我有这两张表（Moodle2.8）：CREATETABLE`mdl_course`(`id`bigint(10)NOTNULLAUTO_INCREMENT,`category`bigint(10)NOTNULLDEFAULT\'0\',`sortorder`bigint(1 查看详情

如何在 MYSQL 中优化此查询？需要做啥

】如何在MYSQL中优化此查询？需要做啥【英文标题】：HowcanIoptimisethisqueryinMYSQL?Whatneedstobedone如何在MYSQL中优化此查询？需要做什么【发布时间】：2010-08-1900:40:40【问题描述】：请向下滚动到“25/08/2010更新”。我有一个已变成视图... 查看详情

如何在 Firebird 2.1 中优化此查询？

】如何在Firebird2.1中优化此查询？【英文标题】：HowcanIoptimizethisqueryinFirebird2.1?【发布时间】：2012-03-0103:47:12【问题描述】：我正在使用Firebird2.1，我需要一些帮助来优化此查询：（可能通过将IN-s替换为JOINS或其他方式来加快速度... 查看详情

优化 postgres 搜索查询的问题

】优化postgres搜索查询的问题【英文标题】：Issuesoptimizingpostgressearchquery【发布时间】：2014-05-2906:08:58【问题描述】：我在使用以下PostgreSQL查询时遇到问题，运行时间超过10秒有没有办法将此查询加速到合理的速度，我只是在寻... 查看详情

Postgres：优化查询

】Postgres：优化查询【英文标题】：Postgres:OptimizeQuery【发布时间】：2022-01-2406:41:21【问题描述】：我有一种情况，我需要显示已发布学习材料或测试的科目列表。下面的查询有效，但大约需要8秒。有没有更好的方法可以优化此... 查看详情

如何优化在 postgresql 中查询这些数据？

】如何优化在postgresql中查询这些数据？【英文标题】：HowcanIoptimizequeryingthisdatainpostgresql?【发布时间】：2014-10-0904:00:50【问题描述】：我有一个查询对于特定行来说很慢。Postgres选择对某些行使用SeqScan而不是使用IndexScan，因为它... 查看详情

如何在子查询中使用 select 语句？ (Postgres)

】如何在子查询中使用select语句？(Postgres)【英文标题】：HowcouldIusetheselectstatementonfeaturefromasubquery?(Postgree)【发布时间】：2021-06-1922:13:21【问题描述】：我正在接受面试培训并试图解决一个问题，我想为每个城市找出花费最多的... 查看详情

最佳实践：优化postgres查询性能（上）(代码片段)

在过去的五年里，我们了解了很多关于如何优化Postgres性能的知识。在这本电子书中，我们写下了如何最大限度地利用你的数据库的关键体会。你是否曾收到过你的团队提出的问题：为什么你们的产品应用运行缓慢... 查看详情

最佳实践：优化postgres查询性能（上）(代码片段)

在过去的五年里，我们了解了很多关于如何优化Postgres性能的知识。在这本电子书中，我们写下了如何最大限度地利用你的数据库的关键体会。你是否曾收到过你的团队提出的问题：为什么你们的产品应用运行缓慢... 查看详情

Postgres：按日期时间优化查询

】Postgres：按日期时间优化查询【英文标题】：Postgres:Optimizingqueryingbydatetime【发布时间】：2013-05-1410:01:04【问题描述】：我有一个包含日期时间字段“updated_at”的表。我的很多查询将使用范围查询来查询此字段，例如update_at>... 查看详情

优化 Postgres 中的 case/decode 查询

】优化Postgres中的case/decode查询【英文标题】：Optimisecase/decodequeryinPostgres【发布时间】：2018-11-0906:03:07【问题描述】：谁能帮助我以另一种方式实现以下查询？我面临性能问题，因为在多个CASE语句中到处都使用了相同的多个SELECT... 查看详情

如何优化此 SQL 查询

】如何优化此SQL查询【英文标题】：HowcanIoptimisethisSQLquery【发布时间】：2011-12-1313:06:25【问题描述】：我正在编写一个软件，用于识别已放在网络服务器(CMS)上但不再需要且应该/可以删除的文件。首先，我尝试手动重现所有必需... 查看详情

如何检测在 Postgres 中持有锁的查询？

】如何检测在Postgres中持有锁的查询？【英文标题】：HowtodetectquerywhichholdsthelockinPostgres?【发布时间】：2014-12-1619:52:32【问题描述】：我想不断跟踪postgres中的互锁。我遇到了LocksMonitoring文章并尝试运行以下查询：SELECTbl.pidASblocked... 查看详情

优化 Postgres 查询

】优化Postgres查询【英文标题】：OptimizePostgresQuery【发布时间】：2013-09-2703:52:16【问题描述】：我注意到我的代码中有以下查询，并想检查这是否可以优化。UPDATEtableasT1SETC1=?FROM(SELECTC2,C3,C4FROMtableWHEREC1=?andcurrent_timestamp>=C5ORDERBYC... 查看详情

Postgres如何创建一个在查询中使用的函数

】Postgres如何创建一个在查询中使用的函数【英文标题】：PostgreshowcanIcreateafunctiontouseinsideaquery【发布时间】：2016-02-0418:08:55【问题描述】：我对postgres比较陌生，并且正在使用9.4版本。我在Java中有一个简单的函数，有人告诉我... 查看详情

Postgres 中的慢查询优化

】Postgres中的慢查询优化【英文标题】：SlowqueryoptimisationinPostgres【发布时间】：2019-06-0410:19:02【问题描述】：我们遇到了特定SQL查询的性能问题，我们正在尝试找出如何改进这里。它的执行时间大约是20-100秒！这是查询和解释：... 查看详情

如何在 jpa 查询中调用 postgres position() 函数

】如何在jpa查询中调用postgresposition()函数【英文标题】：Howtocallpostgresposition()functioninjpaquery【发布时间】：2022-01-1514:01:06【问题描述】：我想从INoperator中给出的id排序的数据库中检索帖子我正在使用下面的SpringDataJPA查询从数据... 查看详情

使用窗口函数编写此 postgres 查询时如何避免 sum(sum())？

】使用窗口函数编写此postgres查询时如何避免sum(sum())？【英文标题】：howtoavoidsum(sum())whenwritingthispostgresquerywithwindowfunctions?【发布时间】：2020-11-1703:38:52【问题描述】：https://www.db-fiddle.com/f/s-s-rpQyyajYdZkkkAJBaYUp/0的可运行查询示例... 查看详情