zabbix=# explain analyze SELECT DISTINCT g.graphid,g.name FROM graphs g,graphs_items gi,items i,hosts_groups hg,hosts h WHERE NOT EXISTS (SELECT NULL FROM graphs_items gi,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='19' WHERE g.graphid=gi.graphid AND gi.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 NOT EXISTS (SELECT NULL FROM items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='19' WHERE g.ymin_type=2 AND g.ymin_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 NOT EXISTS (SELECT NULL FROM items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='19' WHERE g.ymax_type=2 AND g.ymax_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 hg.groupid='66' AND hg.hostid=i.hostid AND gi.graphid=g.graphid AND i.itemid=gi.itemid AND g.graphid=gi.graphid AND h.hostid=i.hostid AND h.status<>3 AND g.flags IN ('0','4') zabbix-# ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- HashAggregate (cost=4472773.61..4472779.51 rows=5892 width=41) (actual time=591895.179..591895.196 rows=2 loops=1) -> Nested Loop (cost=1.41..4472708.46 rows=13031 width=41) (actual time=826.908..591895.161 rows=3 loops=1) Join Filter: (hg.hostid = h.hostid) -> Nested Loop (cost=1.12..4468525.04 rows=14041 width=57) (actual time=826.895..591895.134 rows=3 loops=1) -> Nested Loop (cost=0.84..4461249.48 rows=24557 width=49) (actual time=19.519..591500.777 rows=191544 loops=1) -> Nested Loop (cost=0.42..4445406.17 rows=24557 width=49) (actual time=19.513..590730.010 rows=191544 loops=1) -> Seq Scan on graphs g (cost=0.00..4421583.64 rows=5892 width=41) (actual time=19.502..590188.195 rows=45118 loops=1) Filter: ((flags = ANY ('{0,4}'::integer[])) AND (NOT (SubPlan 2)) AND (NOT (SubPlan 3)) AND (NOT (SubPlan 1))) Rows Removed by Filter: 4947 SubPlan 2 -> HashAggregate (cost=13.31..13.32 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=47213) Filter: ((max(r_1.permission) < 2) OR (min(r_1.permission) IS NULL) OR (min(r_1.permission) = 0)) -> Result (cost=0.85..13.28 rows=3 width=12) (actual time=0.000..0.000 rows=0 loops=47213) One-Time Filter: (g.ymin_type = 2) -> Nested Loop Left Join (cost=0.85..13.28 rows=3 width=12) (never executed) -> Nested Loop (cost=0.71..12.73 rows=3 width=16) (never executed) -> Index Scan using items_pkey on items i_2 (cost=0.42..8.42 rows=1 width=8) (never executed) Index Cond: (g.ymin_itemid = itemid) -> Index Only Scan using hosts_groups_1 on hosts_groups hgg_1 (cost=0.29..4.30 rows=3 width=16) (never executed) Index Cond: (hostid = i_2.hostid) Heap Fetches: 0 -> Index Scan using rights_2 on rights r_1 (cost=0.15..0.18 rows=1 width=12) (never executed) Index Cond: (id = hgg_1.groupid) Filter: (groupid = 19::bigint) SubPlan 3 -> HashAggregate (cost=13.31..13.32 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=47213) Filter: ((max(r_2.permission) < 2) OR (min(r_2.permission) IS NULL) OR (min(r_2.permission) = 0)) -> Result (cost=0.85..13.28 rows=3 width=12) (actual time=0.000..0.000 rows=0 loops=47213) One-Time Filter: (g.ymax_type = 2) -> Nested Loop Left Join (cost=0.85..13.28 rows=3 width=12) (never executed) -> Nested Loop (cost=0.71..12.73 rows=3 width=16) (never executed) -> Index Scan using items_pkey on items i_3 (cost=0.42..8.42 rows=1 width=8) (never executed) Index Cond: (g.ymax_itemid = itemid) -> Index Only Scan using hosts_groups_1 on hosts_groups hgg_2 (cost=0.29..4.30 rows=3 width=16) (never executed) Index Cond: (hostid = i_3.hostid) Heap Fetches: 0 -> Index Scan using rights_2 on rights r_2 (cost=0.15..0.18 rows=1 width=12) (never executed) Index Cond: (id = hgg_2.groupid) Filter: (groupid = 19::bigint) SubPlan 1 -> GroupAggregate (cost=1.27..784.89 rows=13 width=12) (actual time=12.479..12.479 rows=0 loops=47213) Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0)) Rows Removed by Filter: 1 -> Nested Loop Left Join (cost=1.27..784.68 rows=13 width=12) (actual time=5.400..12.473 rows=9 loops=47213) -> Nested Loop (cost=1.12..782.31 rows=13 width=16) (actual time=5.392..12.441 rows=9 loops=47213) Join Filter: (i_1.hostid = hgg.hostid) Rows Removed by Join Filter: 51029 -> Index Only Scan using hosts_groups_1 on hosts_groups hgg (cost=0.29..350.54 rows=12173 width=16) (actual time=0.003..1.874 rows=12169 loops =47213) Heap Fetches: 29224847 -> Materialize (cost=0.84..66.59 rows=5 width=8) (actual time=0.000..0.000 rows=4 loops=574534997) -> Nested Loop (cost=0.84..66.56 rows=5 width=8) (actual time=0.187..0.235 rows=4 loops=47213) -> Index Scan using graphs_items_2 on graphs_items gi_1 (cost=0.42..24.43 rows=5 width=8) (actual time=0.181..0.216 rows=4 loops=4 7213) Index Cond: (g.graphid = graphid) -> Index Scan using items_pkey on items i_1 (cost=0.42..8.43 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=198019) Index Cond: (itemid = gi_1.itemid) -> Index Scan using rights_2 on rights r (cost=0.15..0.18 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=445385) Index Cond: (id = hgg.groupid) Filter: (groupid = 19::bigint) Rows Removed by Filter: 4 -> Index Scan using graphs_items_2 on graphs_items gi (cost=0.42..4.04 rows=5 width=16) (actual time=0.007..0.009 rows=4 loops=45118) Index Cond: (graphid = g.graphid) -> Index Scan using items_pkey on items i (cost=0.42..0.64 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=191544) Index Cond: (itemid = gi.itemid) -> Index Only Scan using hosts_groups_1 on hosts_groups hg (cost=0.29..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=191544) Index Cond: ((hostid = i.hostid) AND (groupid = 66::bigint)) Heap Fetches: 0 -> Index Scan using hosts_pkey on hosts h (cost=0.28..0.29 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3) Index Cond: (hostid = i.hostid) Filter: (status <> 3) Total runtime: 591895.692 ms (70 строк)