sql - 性能查询 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")

我的函数

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

函数性能慢

For ... Loop

您对性能改进有什么建议吗？请帮助我!

Answer 1

只需在 insert 中使用 join 。 . .选择:

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;

或者也许:

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;

请注意，这将返回重复项，但这可能是可取的。