This is a case how did we tune a SQL has performance problem caused by logic defect

Problem

There was a webpage reported getting no response and time out.

Trouble shooting

We identified the SQL from top SQLs quicly, 

Check its performance statistics data,

Disks reads is very high, even higher than Consistent Gets. This is unusal!

Click "SQL Tree" tab, then click "Analyze" to analyze this SQL in further,

We noticed high Consistent Gets and high Temp Space usage in the first time-out subquery. Further reviewed its execution plan, we saw a Cartesian Join! Looked into this SQL, we found a view/table "content_objects" did not join with any of other table/views!

Took it off, then run this subquery again 

It finished in 2 seconds, and has much less Consistent Gets.

Double checked with the developer, this view is added by accidently, we could remove safely.

 Now, the SQL is running fast, and the webpage can show content efficiently.