Thread

Problem with RULE system with condition between 3 tables.

Vadim I. Passynkov <vip@axxent.ca> — 2000-11-14T22:24:58Z

Hi All,

I create rule with condition between 2 different tables, it's working
OK.
sysname - pkey in routers_snmp and routers_name tables. 
sysname,index - pkey in interfaces_name table.

CREATE RULE "send_signal_if_update_interfaces_name" AS ON UPDATE TO
interfaces_name
WHERE
old.monitor_ov_enable <> new.monitor_ov_enable AND
old.sysname = routers_snmp.sysname AND
routers_snmp.monitor_ov_enable = 't'
DO SELECT send_signal ();

EXPLAIN UPDATE interfaces_name SET monitor_ov_enable = 't' where sysname
= 'virgin.tor' and index = 1 ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..10.21 rows=4 width=24)
  ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=12)
  ->  Index Scan using routers_snmp_pkey on routers_snmp 
(cost=0.00..8.13 rows=4 width=12)

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..30.74 rows=836 width=95)
  ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=91)
  ->  Seq Scan on routers_snmp (cost=0.00..20.36 rows=836 width=4)

But if create same RULE between 3 different tables, it will huge timeout
with high postgres cpu load.
Nested Loop rows in 1 case - 836 in 2 case - 701404 !!!!!!!! that the
reason.
Regular SELECT, UPDATE, DELETE operations between this three tables
working fine.
What the problem?

CREATE RULE "send_signal_if_update_interfaces_name" AS ON UPDATE TO
interfaces_name
WHERE
old.monitor_ov_enable <> new.monitor_ov_enable AND
old.sysname = routers_name.sysname AND ----------------------\
routers_name.admin_status = 10 AND----------------------------- two new
with query to third table
old.sysname = routers_snmp.sysname AND
routers_snmp.monitor_ov_enable = 't'
DO SELECT send_signal ();


EXPLAIN UPDATE interfaces_name SET monitor_ov_enable = 't' where sysname
= 'virgin.tor' and index = 1 ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..17.35 rows=1 width=36)
  ->  Nested Loop  (cost=0.00..9.17 rows=1 width=24)
        ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=12)
        ->  Index Scan using routers_name_pkey on routers_name 
(cost=0.00..7.13 rows=1 width=12)
  ->  Index Scan using routers_snmp_pkey on routers_snmp 
(cost=0.00..8.13 rows=4 width=12)

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..21582.82 rows=701404 width=99)
  ->  Nested Loop  (cost=0.00..30.74 rows=836 width=95)
        ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=91)
        ->  Seq Scan on routers_snmp  (cost=0.00..20.36 rows=836
width=4)
  ->  Seq Scan on routers_name  (cost=0.00..17.39 rows=839 width=4)

EXPLAIN


-- 

 Vadim I. Passynkov, Axxent Corp.
 mailto:vip@axxent.ca