sql - 调整 Postgres 查询

Question

我需要根据否定条件加入两个表，这需要很长时间才能执行。

SELECT oola.ship_from_org_id ,
    oola.subinventory,
    oola.line_id ,
    crl.requirement_header_id,
    crl.inventory_item_id
    FROM racesbi_ods.ods_csp_requirement_lines crl
    LEFT JOIN  racesbi_ods.ods_csp_req_line_details crld
        ON crld.requirement_line_id = crl.requirement_line_id
    JOIN racesbi_ods.ods_oe_order_lines_all oola
        ON  crld.source_id               <> oola.line_id
        AND oola.header_id IN
            (SELECT header_id FROM racesbi_ods.ods_oe_order_lines_All
            WHERE line_id = crld.source_id
            )

为了调整这一点，我尝试使用临时表。但我仍然面临性能问题。

create temporary table tst1 --ON commit drop    244067
as(select crl.requirement_header_id,
    crl.inventory_item_id,
    crld.requirement_line_id,
    crld.source_id FROM racesbi_ods.ods_csp_requirement_lines crl
    LEFT JOIN  racesbi_ods.ods_csp_req_line_details crld
        ON crld.requirement_line_id = crl.requirement_line_id
)  distributed randomly;
-- Query returned successfully: 244067 rows affected, 15264 ms execution time.

create temporary table tst2 --ON commit drop    2700951
as(
select ship_from_org_id,
    subinventory,
    line_id
    FROM racesbi_ods.ods_oe_order_lines_all
)  distributed randomly;


create temporary table tst3 --ON commit drop 
as(
select tst1.requirement_header_id,
    tst1.inventory_item_id,
    tst2.ship_from_org_id,
    tst2.subinventory,
    tst2.line_id
    FROM tst1 
    JOIN tst2 ON tst2.line_id != tst1.source_id
)  distributed randomly;

请帮助如何处理 JOIN 中的否定条件

标签： sqlpostgresqlquery-performancesql-tuning