Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why might this query be taking nearly 2 hours to run when similar queries complete within a minute?

CREATE TABLE T3 AS
SELECT 
        A.*, 
        COALESCE(C.FIELD1,0) AS FIELD1A,
        COALESCE(B.FIELD2,0) AS FIELD2A,
        COALESCE(B.FIELD3,0) AS FIELD3A,
        COALESCE(C.FIELD4,0) AS FIELD4A,
        COALESCE(C.FIELD5,0) AS FIELD5A,
        B.KEY1,
        COALESCE(C.FIELD6,0) AS FIELD6A
FROM T1 A
LEFT JOIN T2 B
ON   A.KEY2 = B.KEY1 
AND  B.TIMEFIELD1 <= DATE '28FEB2013'
AND  B.TIMEFIELD2 > DATE '28FEB2013'
AND  B.FIELD7= 'X' 
LEFT JOIN T2 C
ON   A.KEY3 = C.KEY1 
AND  C.TIMEFIELD1  <= DATE '28FEB2013'
AND  C.TIMEFIELD2 > DATE '28FEB2013'
AND  C.FIELD7= 'X' 

DISTRIBUTE ON RANDOM

T1 has about a 400,000 rows; T2 has about a million rows. Both are distributed on random. T3 has the same row count as T1 (as expected).

EXPLAIN suggests the query plan uses nested loops.

(Apologies for the generic column names).

share|improve this question
1  
My wild guess is this point: "Both are distributed on random". –  ypercube Jul 5 '13 at 9:27
    
+1 to the above. To be efficient, the joins need to be collocated, that is, the tables must be distributed by the same set of keys. –  mustaccio Jul 5 '13 at 12:06
    
Are you speaking with specific knowledge of the way Netezza handles distribution? Because all my tables are distributed on random and similar queries with similar volumes do not perform so poorly. –  jl6 Jul 5 '13 at 13:59

1 Answer 1

I have noticed that the optimizer doesn't enforce the constraints on left joins efficiently. I have success in this situation by forcing the constraints in sub queries. Consider revising your query like below:

CREATE TABLE T3 AS
SELECT 
        A.*, 
        COALESCE(C.FIELD1,0) AS FIELD1A,
        COALESCE(B.FIELD2,0) AS FIELD2A,
        COALESCE(B.FIELD3,0) AS FIELD3A,
        COALESCE(C.FIELD4,0) AS FIELD4A,
        COALESCE(C.FIELD5,0) AS FIELD5A,
        B.KEY1,
        COALESCE(C.FIELD6,0) AS FIELD6A
FROM T1 A
LEFT JOIN 
(select * from T2 where TIMEFIELD1 <= DATE '28FEB2013'
AND  TIMEFIELD2 > DATE '28FEB2013'
AND  FIELD7= 'X' ) B
ON   A.KEY2 = B.KEY1 
LEFT JOIN 
(select * from T2  where AND  TIMEFIELD1  <= DATE '28FEB2013'
AND  TIMEFIELD2 > DATE '28FEB2013'
AND  FIELD7= 'X') C
ON   A.KEY3 = C.KEY1 


DISTRIBUTE ON RANDOM
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.