0

I have the following SQL statement:

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;

Execution plan is:

 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

The problem stands out on scanning the index events_1.

The table and the index definitions are:

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)

The statistics are up to date and correct. Also, statistics targets are also correct and enough(cross - checking pg_stats values for columns and getting real counts from table nearly the same).

Yet, even though the statistics are fine the estimation on events_1 is not correct, it is underestimating(I guess due to subplan, not exist part).

How can I convert this not exist to left join with is null? Also, is there any other way to optimize it?

Tried to set enable_indexscan = off;, but it was worse.

Do I simply hit the limits?

Thanks!

Umut TEKİN
  • 856
  • 1
  • 9
  • 19
  • 2
    1) Please learn ANSI SQL join syntax; the join syntax you’re using has been outdated and not considered good practise for 30+ years 2) one obvious issue is that the LEFT JOIN in your sub select is not going to do anything so you might as well remove it - as the sub select is a predicate for the EXISTS, having a left join is not going to affect whether a row exists or not – NickW Jul 20 '23 at 15:59
  • @NickW, the `LEFT JOIN` does contribute because `permission` is a column in `rights` and its value is used to determine which rows the subquery returns to the `EXISTS` operator. – JohnH Jul 20 '23 at 16:10
  • @NickW these aren't joins but Cartesian products – Vivick Jul 20 '23 at 16:20
  • @Vivick No they’re not, the join conditions are in the WHERE clause. This is the old Oracle join syntax that is now outdated – NickW Jul 20 '23 at 16:41
  • @jjanes, it is PostgreSQL 13.7. NickW, as JohnH said it does contribute. The query is generated by a monitoring tool(if I see its developer I will tell him to learn ANSI syntax :) ), it was causing lots of CPU consumption. I wanted to know if there is any way to convert it left outer join. or any other way. top optimize it. – Umut TEKİN Jul 20 '23 at 17:16
  • @NickW, the `LEFT JOIN` is using the ANSI join syntax (if it were the old style Oracle syntax for an outer join, there would be a `(+)` following the optional table). This demonstrates one of the reasons ANSI join syntax should not be mixed with the old style syntax. The ANSI join syntax is much more readable and should always be used. – JohnH Jul 20 '23 at 17:29
  • I'm curious now: if the query is generated by a 3rd party tool, how do you plan on 'changing the sql' ? (I get that you can add extra indexes, I don't see how you can make PG run query X when asked for query Y ... or is this a functionality of PG I'm not aware of?) – deroby Jul 24 '23 at 09:14
  • Hi, @deroby, unfortunately, there is no functionality in PostgreSQL that forces a query to use different, much preferably an old one execution plan. Here, in this question I learnt a new thing(most important thing for me in this case) and let the responsible person for that 3rd party tool to open ticket to optimize it. Indexing is not a good choice because there are other queries and that might get effected. So, I will wait and see. – Umut TEKİN Jul 24 '23 at 12:29

3 Answers3

1

The quick win would be to create this index:

CREATE INDEX ON rights (id, groupid);

VACUUM rights;

If permission is a column of rights, add it to the index in an INCLUDE clause.

The big win would be if you could somehow rewrite the NOT EXISTS subquery so that it doesn't use GROUP BY.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
1

Assuming I correctly understand the posted query and haven't made any typos, the following query should return the same results as the original:

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;

The main performance hit in the original query is the need to execute the EXISTS operation's subquery for each event row. The join criteria in the original subquery are nearly identical to those of the outer query, except that hosts_groups is not filtered in the subquery. This version reduces the redundant table references and eliminates the need to perform per-row subqueries.

I haven't run the query (since I don't have useful sample data to test with), so I await your feedback as to whether it performs better than the original query and returns the correct result set.

JohnH
  • 2,001
  • 1
  • 2
  • 13
1

To convert the NOT EXISTS to a left join, I think you can just remove the f.triggerid test from the WHERE and add f.triggerid as a column into the GROUP BY and the 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
)

And then do a left join to this subquery ON e.objectid = subquery.triggerid and then in outer WHERE test that subquery.triggerid is NULL. This will compute all the aggregates for all of the triggerid in bulk rather than doing each triggerid piecemeal (which might or might not actually be faster, we can't tell just from the info shown)

The query after @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;
Umut TEKİN
  • 856
  • 1
  • 9
  • 19
jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I found a chance to review what you said and put into SQL. Thanks for the recommendation, I learnt new things. It tooks around ~35 seconds, an improvements to @JohnH' s answer. I edited your answer, after it is reviewed by the officers I will mark it as final answer. – Umut TEKİN Jul 21 '23 at 13:18
  • There might be additional improvement by eliminating the `LEFT OUTER JOIN`s by doing the following: change both `LEFT OUTER JOIN`s to just `JOIN`, remove `ftab.triggerid is NULL` from the final `WHERE` clause, and change the `HAVING` clause to be `max(r.permission) >= 2 AND coalesce(min(r.permission), 0) <> 0`. – JohnH Jul 21 '23 at 15:57