postgresql 有什么方法可以优化这个SQL吗？

zqdjd7g9  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(3)|浏览(111)

我有以下SQL语句：

SELECT DISTINCT e.eventid,
                e.objectid,
                e.clock,
                e.ns,
                e.name,
                e.severity
FROM   events e,
       functions f,
       items i,
       hosts_groups hg
WHERE  e.source = '0'
       AND e.object = '0'
       AND NOT EXISTS (SELECT NULL
                       FROM   functions f,
                              items i,
                              hosts_groups hgg
                              LEFT JOIN rights r
                                     ON r.id = hgg.groupid
                                        AND r.groupid IN ( 13, 95, 129, 498,
                                                           853, 1154, 1279, 1429
                                                         )
                       WHERE  e.objectid = f.triggerid
                              AND f.itemid = i.itemid
                              AND i.hostid = hgg.hostid
                       GROUP  BY i.hostid
                       HAVING Max(permission) < 2
                               OR Min(permission) IS NULL
                               OR Min(permission) = 0)
       AND e.objectid = f.triggerid
       AND f.itemid = i.itemid
       AND i.hostid = hg.hostid
       AND hg.groupid IN ( 101, 102, 191, 195,
                           198, 199, 200, 203,
                           206, 320, 324, 402,
                           403, 405, 406, 410,
                           411, 414, 415, 416,
                           417, 420, 421, 422,
                           423, 425, 426, 427,
                           432, 434, 435, 436,
                           437, 438, 441, 503,
                           504, 571, 1230, 1390,
                           1391, 1534, 1840, 1841, 2925 )
       AND e.value = 1
ORDER  BY e.eventid DESC
LIMIT  501;

字符串
执行计划为：

Limit  (cost=176751661.81..176751670.58 rows=501 width=86) (actual time=940324.730..940347.647 rows=501 loops=1)
   Buffers: shared hit=850499789, temp read=19995 written=21317
   ->  Unique  (cost=176751661.81..176760246.35 rows=490545 width=86) (actual time=940324.726..940347.030 rows=501 loops=1)
         Buffers: shared hit=850499789, temp read=19995 written=21317
         ->  Sort  (cost=176751661.81..176752888.18 rows=490545 width=86) (actual time=940324.723..940345.468 rows=1501 loops=1)
               Sort Key: e.eventid DESC, e.objectid, e.clock, e.ns, e.name, e.severity
               Sort Method: external merge  Disk: 17392kB
               Buffers: shared hit=850499789, temp read=19995 written=21317
               ->  Hash Join  (cost=216403.41..176691546.09 rows=490545 width=86) (actual time=5736.438..940072.099 rows=168010 loops=1)
                     Hash Cond: (e.objectid = f.triggerid)
                     Buffers: shared hit=850499780, temp read=19139 written=19139
                     ->  Index Scan using events_1 on events e  (cost=0.56..176432359.99 rows=1177280 width=86) (actual time=1.918..934059.394 rows=2360086 loops=1)
                           Index Cond: ((source = 0) AND (object = 0))
                           Filter: ((value = 1) AND (NOT (SubPlan 1)))
                           Rows Removed by Filter: 2034965
                           Buffers: shared hit=849942562
                           SubPlan 1
                             ->  HashAggregate  (cost=40.78..41.89 rows=25 width=40) (actual time=0.390..0.390 rows=0 loops=2360086)
                                   Group Key: i_1.hostid
                                   Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
                                   Batches: 1  Memory Usage: 24kB
                                   Rows Removed by Filter: 1
                                   Buffers: shared hit=845651503
                                   ->  Nested Loop Left Join  (cost=1.57..40.04 rows=74 width=12) (actual time=0.019..0.354 rows=43 loops=2360086)
                                         Buffers: shared hit=845651503
                                         ->  Nested Loop  (cost=1.28..14.16 rows=16 width=16) (actual time=0.013..0.052 rows=12 loops=2360086)
                                               Buffers: shared hit=48616416
                                               ->  Nested Loop  (cost=0.86..12.49 rows=3 width=8) (actual time=0.008..0.017 rows=2 loops=2360086)
                                                     Buffers: shared hit=27730393
                                                     ->  Index Scan using functions_1 on functions f_1  (cost=0.43..4.55 rows=3 width=8) (actual time=0.003..0.005 rows=2 loops=2360086)
                                                           Index Cond: (triggerid = e.objectid)
                                                           Buffers: shared hit=9551365
                                                     ->  Index Scan using items_pkey on items i_1  (cost=0.43..2.65 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4544491)
                                                           Index Cond: (itemid = f_1.itemid)
                                                           Buffers: shared hit=18178425
                                               ->  Index Only Scan using hosts_groups_1 on hosts_groups hgg  (cost=0.42..0.51 rows=5 width=16) (actual time=0.003..0.008 rows=6 loops=4544491)
                                                     Index Cond: (hostid = i_1.hostid)
                                                     Heap Fetches: 9037804
                                                     Buffers: shared hit=20886023
                                         ->  Index Scan using rights_2 on rights r  (cost=0.29..1.57 rows=5 width=12) (actual time=0.005..0.019 rows=3 loops=28604753)
                                               Index Cond: (id = hgg.groupid)
                                               Filter: (groupid = ANY ('{13,95,129,498,853,1154,1279,1429}'::bigint[]))
                                               Rows Removed by Filter: 29
                                               Buffers: shared hit=797035087
                     ->  Hash  (cost=211065.22..211065.22 rows=325330 width=8) (actual time=2247.572..2267.285 rows=614462 loops=1)
                           Buckets: 262144  Batches: 4  Memory Usage: 8053kB
                           Buffers: shared hit=557218, temp written=1575
                           ->  Gather  (cost=135136.65..211065.22 rows=325330 width=8) (actual time=771.135..1781.991 rows=614462 loops=1)
                                 Workers Planned: 4
                                 Workers Launched: 4
                                 Buffers: shared hit=557218
                                 ->  Parallel Hash Join  (cost=134136.65..177532.22 rows=81332 width=8) (actual time=736.027..1714.938 rows=122892 loops=5)
                                       Hash Cond: (f.itemid = i.itemid)
                                       Buffers: shared hit=557218
                                       ->  Parallel Seq Scan on functions f  (cost=0.00..40599.51 rows=675652 width=16) (actual time=0.028..401.843 rows=535373 loops=5)
                                             Buffers: shared hit=33843
                                       ->  Parallel Hash  (cost=132475.65..132475.65 rows=132880 width=8) (actual time=731.952..731.986 rows=107386 loops=5)
                                             Buckets: 1048576 (originally 524288)  Batches: 1 (originally 1)  Memory Usage: 33376kB
                                             Buffers: shared hit=523175
                                             ->  Nested Loop  (cost=0.97..132475.65 rows=132880 width=8) (actual time=1.923..551.210 rows=107386 loops=5)
                                                   Buffers: shared hit=523175
                                                   ->  Parallel Index Only Scan using hosts_groups_1 on hosts_groups hg  (cost=0.42..3481.74 rows=791 width=8) (actual time=1.742..16.262 rows=464 loops=5)
                                                         Filter: (groupid = ANY ('{101,102,191,195,198,199,200,203,206,320,324,402,403,405,406,410,411,414,415,416,417,420,421,422,423,425,426,427,432,434,435,436,437,438,441,503,504,571,1230,1390,1391,1534,1840,1841,2925}'::bigint[]))
                                                         Rows Removed by Filter: 20657
                                                         Heap Fetches: 29978
                                                         Buffers: shared hit=23995
                                                   ->  Index Scan using items_9 on items i  (cost=0.56..159.99 rows=309 width=16) (actual time=0.023..0.850 rows=232 loops=2318)
                                                         Index Cond: (hostid = hg.hostid)
                                                         Buffers: shared hit=499180
 Planning:
   Buffers: shared hit=196
 Planning Time: 1.830 ms
 Execution Time: 940388.537 ms
(73 rows)

型
https://explain.depesz.com/s/dOQR9
在扫描索引events_1时，该问题非常突出。
表和索引定义为：

zabbix=# \d events
                                 Table "public.events"
    Column    |          Type           | Collation | Nullable |        Default
--------------+-------------------------+-----------+----------+-----------------------
 eventid      | bigint                  |           | not null |
 source       | integer                 |           | not null | 0
 object       | integer                 |           | not null | 0
 objectid     | bigint                  |           | not null | '0'::bigint
 clock        | integer                 |           | not null | 0
 value        | integer                 |           | not null | 0
 acknowledged | integer                 |           | not null | 0
 ns           | integer                 |           | not null | 0
 name         | character varying(2048) |           | not null | ''::character varying
 severity     | integer                 |           | not null | 0
Indexes:
    "events_pkey" PRIMARY KEY, btree (eventid)
    "events_1" btree (source, object, objectid, clock)
    "events_2" btree (source, object, clock)

型
统计数据是最新的，正确的。此外，统计目标也是正确和足够的（交叉检查列的pg_stats值，并从表中获得几乎相同的实际计数）。
然而，尽管统计数据很好，但对事件_1的估计是不正确的，它被低估了（我猜是由于子计划，不存在部分）。
如何将not exist转换为left join with is null？还有其他方法可以优化它吗？
尝试设置enable_indexscan = off;，但情况更糟。
我只是达到了极限吗？
谢谢你，谢谢

postgresql

3条答案

按热度按时间
tmb3ates

tmb3ates1#

要将NOT EXISTS转换为左连接，我认为您可以从WHERE中删除f.triggerid测试，并将f.triggerid作为列添加到GROUP BY和select-list中。

with subquery as (SELECT f.triggerid
   FROM   functions f,
   ...
   GROUP  BY f.triggerid, i.hostid
   HAVING Max(permission) < 2
   OR Min(permission) IS NULL
   OR Min(permission) = 0
)

字符串
然后对这个子查询ON e.objectid = subquery.triggerid执行左联接，然后在外部WHERE测试子查询.triggerid为NULL。这将批量计算所有triggerid的所有聚合，而不是逐个计算每个triggerid（这可能会更快，也可能不会更快，我们不能仅从显示的信息中判断）
@jjanes recommendation后的查询：

with ftab as (
  SELECT 
    f.triggerid 
  FROM 
    functions f, 
    items i, 
    hosts_groups hgg 
    LEFT JOIN rights r ON r.id = hgg.groupid 
    AND r.groupid IN (
      13, 95, 129, 498, 853, 1154, 1279, 1429
    ) 
  WHERE f.itemid = i.itemid 
    AND i.hostid = hgg.hostid 
  GROUP BY 
    i.hostid, 
    f.triggerid 
  HAVING 
    Max(permission) < 2 
    OR Min(permission) IS NULL 
    OR Min(permission) = 0
) 
SELECT 
  DISTINCT e.eventid, 
  e.objectid, 
  e.clock, 
  e.ns, 
  e.name, 
  e.severity 
FROM 
  events e 
  join functions f on e.objectid = f.triggerid 
  join items i on f.itemid = i.itemid 
  join hosts_groups hg on i.hostid = hg.hostid 
  left outer join ftab on e.objectid = ftab.triggerid 
WHERE 
  ftab.triggerid is NULL 
  and e.value = 1 
  and e.source = '0' 
  AND e.object = '0' 
  AND hg.groupid IN (
    101, 102, 191, 195, 198, 199, 200, 203, 
    206, 320, 324, 402, 403, 405, 406, 410, 
    411, 414, 415, 416, 417, 420, 421, 422, 
    423, 425, 426, 427, 432, 434, 435, 436, 
    437, 438, 441, 503, 504, 571, 1230, 1390, 
    1391, 1534, 1840, 1841, 2925
  ) 
ORDER BY 
  e.eventid DESC 
LIMIT 
  501;

型

赞(0）分享  回复(0）举报  2023-08-04
1bqhqjot

1bqhqjot2#

快速的胜利将是创建这个索引：

CREATE INDEX ON rights (id, groupid);

VACUUM rights;

字符串
如果permission是rights的列，则将其添加到INCLUDE子句中的索引中。
如果您能以某种方式重写NOT EXISTS子查询，使其不使用GROUP BY，则会取得巨大的成功。

赞(0）分享  回复(0）举报  2023-08-04
vlurs2pr

vlurs2pr3#

假设我正确理解了发布的查询并且没有做任何错别字，下面的查询应该返回与原始查询相同的结果：

WITH cte AS (
  SELECT e.eventid,
         e.objectid,
         e.clock,
         e.ns,
         e.name,
         e.severity,
         hg.groupid,
         min(r.permission) over event_host_partition AS min_permission,
         max(r.permission) over event_host_partition AS max_permission
    FROM events e
    JOIN functions f
      ON e.objectid = f.triggerid
    JOIN items i
      ON f.itemid = i.itemid
    JOIN hosts_groups hg
      ON i.hostid = hg.hostid
    JOIN rights r
      ON     r.id = hg.groupid
         AND r.groupid IN (13, 95, 129, 498, 853, 1154, 1279, 1429)
   WHERE     e.source = '0'
         AND e.object = '0'
         AND e.value = 1
WINDOW event_host_partition AS (PARTITION BY e.eventid, i.hostid))
SELECT DISTINCT cte.eventid,
                cte.objectid,
                cte.clock,
                cte.ns,
                cte.name,
                cte.severity
  FROM cte
 WHERE     cte.max_permission >= 2
       AND coalesce(cte.min_permission, 0) <> 0
       AND cte.groupid IN (101, 102, 191, 195, 198, 199, 200, 203,
                           206, 320, 324, 402, 403, 405, 406, 410,
                           411, 414, 415, 416, 417, 420, 421, 422,
                           423, 425, 426, 427, 432, 434, 435, 436,
                           437, 438, 441, 503, 504, 571, 1230, 1390,
                           1391, 1534, 1840, 1841, 2925)
 ORDER BY cte.eventid DESC
 LIMIT 501;

字符串
原始查询的主要性能影响是需要为每个事件行执行EXISTS操作的子查询。原始子查询中的连接条件几乎与外部查询中的连接条件相同，只是hosts_groups不在子查询中过滤。此版本减少了冗余的表引用，并消除了执行每行子查询的需要。
我还没有运行这个查询（因为我没有有用的示例数据来测试），所以我等待您的反馈，看看它是否比原始查询执行得更好，并返回正确的结果集。

赞(0）分享  回复(0）举报  2023-08-04
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com