It happened so many times in the past… once again about “the art of full scan”

Let me tell you: there are so little tricks you can utilize when optimizing the OLTP queries, that each time I am writing something about this, I feel like I am repeating myself for the hundredth time… yet… people make the same mistakes again and again, and so here I am preaching the same thing again and again…

The original query which was brought to my attention last Thursday looked like this:

SELECT
cs.id as c_id
...
,(select
count(1)
from
approvals apr inner join customer_sources cs_s on apr.customer_id = cs_s.customer_id
where
apr.processed_on >= app.processed_on - interval '30seconds'
and apr.processed_on = app.processed_on - interval '30seconds'
and apr.processed_on '2014-05-01'
AND app.processed_on <= '2014-05-01 00:01:00'
AND app.teletrack_report_id is not NULL