postgresql - 了解如何使用嵌套循环优化 sql 查询（例如，搜索每个部门员工的最高工资）

Question

数据：

CREATE SEQUENCE employee_id_seq; 

create table Employee
(
    id_emp    int DEFAULT nextval('employee_id_seq')
                           NOT NULL PRIMARY KEY UNIQUE,
    name_emp  varchar(255) NOT NULL,
    mgr_id_fk int          not null,

    job_emp   text         NOT NULL, 
    salary    int          NOT NULL,
    date_emp  date         NOT NULL,
    dep_ID_fk int          NOT NULL
);

ALTER SEQUENCE employee_id_seq
    OWNED BY employee.id_emp; 

create table Manager
(
    id_mgr   int not null primary key unique,
    type_mgr varchar(255)
);

ALTER table Employee
    add FOREIGN KEY (mgr_id_fk) REFERENCES Manager (id_mgr)
        on update cascade 
        on delete set null;

create table Department
(
    id_depart   int          NOT NULL PRIMARY KEY unique,
    name_depart varchar(255) not null,
    address     text,
    phone       text
);

insert into Manager (id_mgr, type_mgr)
VALUES
       (1006, 'juniormgr'),
       (1004, 'middlemgr'),
       (1005, 'seniormgr');

insert into Department (id_depart, name_depart, address, phone)
values (1, 'Sales', 'Sydney', '0425 198 053'),
       (2, 'Accounts', 'Melbourne', '0429 198 955'),
       (3, 'Admin', 'Melbourne', '0428 198 758'),
       (4, 'Marketing', 'Sydney', '0427 198 757');


insert into Employee(id_emp, name_emp, mgr_id_fk, job_emp, salary, date_emp, dep_ID_fk)
values (nextval('employee_id_seq'), 'ken Adams', 1006, 'Salesman', 70000, '2008-04-12', 1),
       (nextval('employee_id_seq'), 'Ru Jones', 1004, 'Salesman', 65000, '2010-01-18', 1),
       (nextval('employee_id_seq'), 'Dhal Sim', 1006, 'Accountant', 88000, '2001-03-07', 2),
       (nextval('employee_id_seq'), 'Ellen Honda', 1006, 'Manager', 118000, '2001-03-17', 1),
       (nextval('employee_id_seq'), 'Mike Bal', 1005, 'Receptionist', 68000, '2006-06-21', 3),
       (nextval('employee_id_seq'), 'Martin Bison', 1005, 'CEO', 210000, '2010-07-12', 3),
       (nextval('employee_id_seq'), 'Shen Li', 1004, 'Salesman', 86000, '2014-09-18', 1),
       (nextval('employee_id_seq'), 'Zang Ross', 1004, 'Salesman', 65000, '2017-02-02', 1),
       (nextval('employee_id_seq'), 'Sagar Kahn', 1005, 'Salesman', 70000, '2016-03-01', 1);

由于嵌套循环（伴随的子查询），这样的查询具有运行时复杂性（在我的理解中） - O (n^2)：

select e.salary, e.job_emp, d.name_depart from employee e
join department d on e.dep_id_fk = d.id_depart
where not exists (select 1 from employee e2
                  where e2.dep_id_fk = e.dep_id_fk
                    and e2.salary > e.salary)
group by e.salary, e.job_emp,  d.name_depart order by e.salary desc;

您将如何开始优化这样的查询？

你能描述什么算法来解决这个问题？

我的意思是，您使用什么技术来分析问题（例如查询计划等）？

标签： postgresql