1

Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key. Table B has columns (P3, P1, Q1) where P3 is the primary key. Table C has columns (P2, Q1, Value2) where P2 and Q1 are a multi column primary key.

I need to delete all rows from table A which don't have corresponding data in table B and C meaning to delete (p1,p2,v1) from A if there is no (p3,p1,q1) in B and (p2, q1, v2) in B for any q1 and v2.

Currently the query is:

DELETE FROM A where concat(P1, P2) NOT IN
(
SELECT Distinct On (A.P1, A.P2)
        concat(A.P1, A.P2)
        from C
        INNER JOIN B
        ON C.Q1 = B.Q1
        INNER JOIN A
        ON A.P1 = B.P1
        WHERE A.P2 = C.P2
)

but this query never finishes on the tables where table A and C have millions of rows. How can I optimize the query?

Edit: I am trying to achieve what I wrote with the purpose of cleaning up table A periodically when its data becomes irrelevant which is determined by whether the data appears in tables B and C or not.

Edit2: Improved version:

DELETE FROM A where NOT EXISTS
    (
    SELECT 1
            from C
            INNER JOIN B
            ON C.Q1 = B.Q1
            AND A.P1 = B.P1
            AND A.P2 = C.P2
    )

Three questions:

  1. Is using AND inside ON like that faster than using WHERE?
  2. Can "with" be utilized to improve this further?
  3. Any other suggestions of optimization?
  • Don't use concat, use column references, e.g: where (P1, P2) NOT IN (SELECT b.p3, b.p1 ...) the second join to A in the sub-select also looks dubious. Alternatively try a NOT EXISTS query – a_horse_with_no_name Aug 30 '18 at 18:11
  • 2
    I don't really understand what you are trying to achieve (and obfuscating all the column and table names doesn't help either). Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. – a_horse_with_no_name Aug 30 '18 at 18:11
  • @a_horse_with_no_name thanks for your answer, it already improved the query a lot. I am trying to achieve what I wrote with the purpose of cleaning up table A periodically when its data becomes irrelevant which is determined by whether the data appears in tables B and C or not. Is using a "with" statement instead of a subquery better? – Guy L. Aug 30 '18 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.