Performance query PostgreSQL

Question

# table tbl_a
   ID
   Name
> tbl_a: 10000 record
CREATE INDEX ID on tbl_a USING btree (ID COLLATE pg_catalog."default")

# table tbl_b
   Branch
   ID
   name
> tbl_b: 1000 record
CREATE INDEX ID on tbl_b USING btree (ID COLLATE pg_catalog."default")

My function

CREATE OR REPLACE FUNCTION name_func()
RETURNS SETOF  AS
$BODY$DECLARE
  _r record;
BEGIN
 CREATE TEMP TABLE tmp_table AS
 SELECT branch, ID, Name from tbl_b where 1 = 0;
 FOR _r IN SELECT branch, ID, Name from tbl_b order by name
 LOOP
    INSERT INTO tmp_table 
    SELECT _r.branch, ID, Name FROM tbl_a  where ID = _r.ID and Name = _r.Name;
 END LOOP;
 //do something with tmp_table 
END

function performance slow in

For ... Loop

Do you have any advice for performance improvements? Please help me!

Never use for loops in plpgsql if you care about performance — 404
– 404, Commented Aug 21, 2018 at 11:22
Are you sure that this FOR LOOP is a problem. I create tables with 25000 records in tbl_a, and 1000 in tbl_b and used the functions. It is done in 33 ms in average. And second thing what is relation between tbl_a, tbl_b? 1:1, 1:N, N:1. And why there is no unique ID on any of them? — Grzegorz Grabek
– Grzegorz Grabek, Commented Aug 21, 2018 at 12:11

Gordon Linoff · Accepted Answer · 2018-08-21 12:31:47Z

2

Just use join in your insert . . . select:

INSERT INTO tmp_table (id, name)
    SELECT a.ID, a.Name
    FROM tbl_a a JOIN
         tbl_b b
         ON a.id = b.id AND a.name b.Name;

Or perhaps:

INSERT INTO tmp_table (id, name)
    SELECT a.ID, a.Name
    FROM tbl_a a 
    INTERSECT
    SELECT b.ID, b.Name
    FROM tbl_b b;

Note that this will return duplicates, but that might be desirable.

edited Aug 21, 2018 at 12:31

answered Aug 21, 2018 at 11:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Koen Over a year ago

The WHERE condition in the first option seems a bit odd.

Collectives™ on Stack Overflow

Performance query PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related