0

I have a requiremnt to join two tables based upon negation condtion, which is taking much time to execute.

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
            )

In order to tune this I tried using temporary tables. but still I'm facing performance issue.

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;

Kindly help how to handle negation condition in JOINs

Pooja
  • 327
  • 1
  • 5
  • 20
  • 1
    Sample data, desired results, and a database tag would help (psql <> postgresql). – Gordon Linoff Oct 22 '18 at 11:12
  • 3
    Please **[edit]** your question and add the `create table` statements for the tables in question (including all indexes) and the [execution plans](https://www.postgresql.org/docs/current/static/using-explain.html) generated using **`explain (analyze, buffers)`** - not just a simple explain [**Formatted text**](http://stackoverflow.com/help/formatting) please no screenshots –  Oct 22 '18 at 11:16
  • It seems your query is wrong. If you do an outer join to `ods_csp_req_line_details`, then you cannot use a column of it (`ON crld.source_id` <> ...) on an inner join. That defeats the purpose of the outer join. – The Impaler Oct 22 '18 at 13:49

0 Answers0