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 )
- Is using AND inside ON like that faster than using WHERE?
- Can "with" be utilized to improve this further?
- Any other suggestions of optimization?