version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.6 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
(1 row)

do
$body$
declare
begin
create table p
(x int, y int, z text);

create unique index p_x on p using btree(x);

create table p_1 (check(y = 1::int)) INHERITS (p);
create table p_2 (check(y = 2::int)) INHERITS (p);
create table p_3 (check(y = 3::int)) INHERITS (p);

alter table p_1 add constraint pk1 primary key (x);
alter table p_2 add constraint pk2 primary key (x);
alter table p_3 add constraint pk3 primary key (x);


CREATE OR REPLACE FUNCTION p_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

	IF (NEW.y = 1::int) THEN 
		insert into p_1 values (NEW.*);
	ELSIF (NEW.y = 2::int) THEN 
		insert into p_2 values (NEW.*);
	ELSIF (NEW.y = 3::int) THEN 
		insert into p_3 values (NEW.*);
	END IF;

	RETURN NULL;

END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER p_insert_trigger
    BEFORE INSERT ON p
    FOR EACH ROW EXECUTE PROCEDURE p_insert_trigger();




insert into p select n, 1, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from generate_series(1,300000) as t(n);
insert into p select n, 2, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from generate_series(1,300000) as t(n);
insert into p select n, 3, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from generate_series(1,300000) as t(n);



analyze p;
analyze p_1;
analyze p_2;
analyze p_3;


create table to_insert as
	select floor(random() * 100 + 1) as x, floor(random() * (3-1+1) + 1) as y, 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'::text as z from generate_series(1,100);


end;
$body$ language plpgsql;


explain (analyze, buffers)
	update p
		set z = to_insert.z
	from to_insert 
	where p.x::int = to_insert.x::int
	      and p.y::int = to_insert.y::int;


                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Update on p  (cost=0.01..20150.10 rows=16 width=52) (actual time=2.568..2.568 rows=0 loops=1)
   Update on p
   Update on p_1
   Update on p_2
   Update on p_3
   Buffers: shared hit=1786 read=1 dirtied=1
   ->  Hash Join  (cost=0.01..31.43 rows=1 width=52) (actual time=0.060..0.060 rows=0 loops=1)
         Hash Cond: (((to_insert.x)::integer = p.x) AND ((to_insert.y)::integer = p.y))
         ->  Seq Scan on to_insert  (cost=0.00..20.70 rows=1070 width=54) (never executed)
         ->  Hash  (cost=0.00..0.00 rows=1 width=14) (actual time=0.047..0.047 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on p  (cost=0.00..0.00 rows=1 width=14) (actual time=0.046..0.046 rows=0 loops=1)
   ->  Nested Loop  (cost=0.42..6706.22 rows=5 width=52) (actual time=0.097..0.632 rows=29 loops=1)
         Buffers: shared hit=414
         ->  Seq Scan on to_insert  (cost=0.00..20.70 rows=1070 width=54) (actual time=0.040..0.082 rows=100 loops=1)
               Buffers: shared hit=2
         ->  Index Scan using pk1 on p_1  (cost=0.42..6.24 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=100)
               Index Cond: (x = (to_insert.x)::integer)
               Filter: ((to_insert.y)::integer = y)
               Rows Removed by Filter: 1
               Buffers: shared hit=412
   ->  Nested Loop  (cost=0.42..6706.22 rows=5 width=52) (actual time=0.053..0.259 rows=34 loops=1)
         Buffers: shared hit=421
         ->  Seq Scan on to_insert  (cost=0.00..20.70 rows=1070 width=54) (actual time=0.028..0.044 rows=100 loops=1)
               Buffers: shared hit=2
         ->  Index Scan using pk2 on p_2  (cost=0.42..6.24 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=100)
               Index Cond: (x = (to_insert.x)::integer)
               Filter: ((to_insert.y)::integer = y)
               Rows Removed by Filter: 1
               Buffers: shared hit=419
   ->  Nested Loop  (cost=0.42..6706.22 rows=5 width=52) (actual time=0.024..0.274 rows=37 loops=1)
         Buffers: shared hit=416
         ->  Seq Scan on to_insert  (cost=0.00..20.70 rows=1070 width=54) (actual time=0.011..0.033 rows=100 loops=1)
               Buffers: shared hit=2
         ->  Index Scan using pk3 on p_3  (cost=0.42..6.24 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=100)
               Index Cond: (x = (to_insert.x)::integer)
               Filter: ((to_insert.y)::integer = y)
               Rows Removed by Filter: 1
               Buffers: shared hit=414
 Planning time: 2.224 ms
 Execution time: 2.784 ms
(41 rows)

DO
$$
declare _rec record;
BEGIN
  for _rec in (select distinct y from to_insert) 
    LOOP
       	update p
		set z = to_insert.z
	from to_insert 
	where p.x::int = to_insert.x::int
	      and p.y::int = to_insert.y::int
              and p.y::int = _rec.y::int;
    END LOOP;

END;
$$ language plpgsql;