Complex Query Optimization

Running Postres 7.4 (yep upgrading)开发者_StackOverflow中文版

This is a view query and it's used a lot but takes longer than I would like. Any Optimization Suggestions? Any field with a Id in the name is indexed

SELECT  
db_tbl_1.field_id, s."Field ID" AS foreign_field_id,
s."Field Name" AS field_name,
CASE    
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 3) = '01' THEN 'Field Label 1'
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 4) = '321' THEN 'Field Label 1' 
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 5) = '1234' THEN 'Field Label 1'
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 6) = '55555' THEN 'Field Label 1'    
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 3) = '76' THEN 'Field Label 2' 
END AS new_field, s.field_1,
db_tbl_2.field_2, db_tbl_1.field_2_a, 
db_tbl_1.field_3, db_tbl_1.field_4,
db_tbl_1.field_5, db_tbl_1.field_6, db_tbl_1.field_date_1,
db_tbl_1.field_7, db_tbl_2.field_8 AS new_field_name,
s."Field Date" AS field_date, db_tbl_1.created_date,
CASE
    WHEN (((DATE_TRUNC('month', "Field Date") + INTERVAL '2 MONTH') - "Field Date" > 60) AND 
         ((DATE_TRUNC('month', "Field Date") + INTERVAL '2 MONTH') - "Field Date" <= 92))
    THEN (DATE_TRUNC('month', "Field Date") + INTERVAL '2 MONTH')
    -- the most days in a three month span can be 92
    ELSE (DATE_TRUNC('month', "Field Date") + INTERVAL '3 MONTH')
END AS next_date
FROM db_schema_1.db_tbl_1, db_schema_1.db_tbl_2, "Table S" AS s
WHERE db_tbl_1.program_level_id = db_tbl_2.id
AND db_tbl_1.field_id = s."Another ID"
AND (CASE   
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 3) = '01' THEN 1
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 4) = '321' THEN 1 
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 5) = '1234' THEN 1
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 6) = '55555' THEN 1  
    WHEN SUBSTRING(s."Field ID" FROM 0 FOR 3) = '76' THEN 1
    ELSE 0 
END) = 1

---

Try to change the last statement, where you are using the case statement because the query will evaluate all of them. and then check if is equals to 1, you should try and "OR" statement, because if one is correct, it will not evaluate the other alternatives. It should help you a bit. Something like this.

AND ( SUBSTRING(s."Field ID" FROM 0 FOR 3) = '01' OR
    SUBSTRING(s."Field ID" FROM 0 FOR 4) = '321' OR
    SUBSTRING(s."Field ID" FROM 0 FOR 5) = '1234' OR
    SUBSTRING(s."Field ID" FROM 0 FOR 6) = '55555' OR
    SUBSTRING(s."Field ID" FROM 0 FOR 3) = '76'
)