I'm building a reporting dashboard from an event log which our company does not have write access to.
To get the information out in any meaningful I've written this very large sql query. Unfortunately it is taking a long time to load and somethings causes the DB to hang.
I have no write access, so I can't create any views, indexes, and need to find a way to get it quicker just through the query itself
Can anyone see any ways to speed up the query itself?
select
s2.start_date as date_updated,
d2.special_day as scheduled_day,
d2.donor as donor,
s2.status,
s2.recipient_org as scheduled_charity,
s2.start_time,
s2.end_time,
e3.offered_amount,
e3.offered_time,
e3.multi_offer,
e3.accepted_amount,
e3.accepted_time,
e3.rejected_amount,
e3.noavailability_amount,
e3.rejected_time,
e3.available_amount,
e3.available_time,
e3.collected_amount,
e3.collected_time,
e3.noresponse_amount,
e3.noresponse_time,
r2.accepted_charity
from
(
select
generate_series(
'2017-03-01'::date,
now()::date + 7,
'1 day'::interval
)::date as special_day,
external_id as donor
from
(
select
e1.external_id as external_id,
max( e1."timestamp" ) as last_updated
from
(
select
*
from
donor_event e2
order by
e2."timestamp" desc
) e1
group by
e1.external_id
) d1
) d2 left join(
select
r1.donor_id,
r1.created::date as created,
r1.recipient_id as accepted_charity
from
donation_event r1
where
r1.event_type = 'Accepted'
group by
r1.donor_id,
r1.recipient_id,
r1.created::date
) r2 on
r2.donor_id = d2.donor
and r2.created = d2.special_day left join(
select
s1."timestamp"::date as start_date,
s1.status,
s1.donor_org,
s1.end_time,
s1.start_time,
-- Converts day of week to ISO week and adds one day for donor schedule bug
-- Use modular arithmetic to add 1 day
case
when(
s1.donor_org like '%DONOR%'
) then(
s1.day_of_week + 1
)% 7
else s1.day_of_week % 7
end as day_of_week,
s1.recipient_org,
lead(
s1."timestamp",
1
) over(
partition by s1.donor_org::text || s1.day_of_week::text
order by
s1.donor_org,
s1.day_of_week,
s1."timestamp"
)::date as finish_date
from
schedule_event s1
) s2 on
extract(
dow
from
special_day
)= s2.day_of_week
and d2.donor = s2.donor_org
and(
(
d2.special_day >= s2.start_date
and d2.special_day < s2.finish_date
)
or(
s2.finish_date is null
and d2.special_day >= s2.start_date
)
) left join(
select
e1.donor_id,
e1.created::date,
case
when(
count(*) filter(
where
e1.event_type = 'Offered'
)> 0
) then 1
else 0
end as offered_amount,
case
when(
count(*) filter(
where
e1.event_type = 'Available'
)> 0
) then 1
else 0
end as available_amount,
min( e1.created::time ) filter(
where
e1.event_type = 'Available'
) as available_time,
min( e1.created::time ) filter(
where
e1.event_type = 'Offered'
) as offered_time,
case
when(
count(*) filter(
where
e1.event_type = 'NoResponse'
)> 0
) then 1
else 0
end as noresponse_amount,
case
when(
count(*) filter(
where
e1.event_type = 'NoAvailability'
)> 0
) then 1
else 0
end as noavailability_amount,
min( e1.created::time ) filter(
where
e1.event_type = 'NoResponse'
) as noresponse_time,
case
when(
count(*) filter(
where
e1.event_type = 'Collected'
)> 0
) then 1
else 0
end as collected_amount,
min( e1.created::time ) filter(
where
e1.event_type = 'Collected'
) as collected_time,
case
when(
count(*) filter(
where
e1.event_type = 'Rejected'
)> 0
) then 1
else 0
end as rejected_amount,
min( e1.created::time ) filter(
where
e1.event_type = 'Rejected'
) as rejected_time,
case
when(
count(*) filter(
where
e1.event_type = 'Accepted'
)> 0
) then 1
else 0
end as accepted_amount,
min( e1.created::time ) filter(
where
e1.event_type = 'Accepted'
) as accepted_time,
e2.charity as multi_offer
from
donation_event e1 left join(
select
e1.created,
e1.donor_id,
count( aggregate_id ) filter(
where
e1.charity > 1
) as charity
from
(
select
created::date,
donor_id,
aggregate_id,
count( distinct recipient_id ) as charity
from
donation_event
where
(
recipient_id is not null
or recipient_id <> ''
)
and aggregate_id <> ''
and event_type = 'Offered'
group by
created::date,
donor_id,
aggregate_id
) e1
group by
e1.created,
e1.donor_id
) e2 on
e2.created::date = e1.created::date
and e1.donor_id = e2.donor_id
group by
e1.donor_id,
e1.created::date,
e2.charity
order by
e1.created::date desc
) e3 on
e3.donor_id = d2.donor
and e3.created = d2.special_day
order by
d2.donor,
d2.special_day
Here's The Explain Analyse on the query itself.
Merge Left Join (cost=2000118.61..28935637.09 rows=5936437650 width=153) (actual time=80230.779..83546.009 rows=1223704 loops=1)
Merge Cond: ((d2.donor = e3.donor_id) AND (d2.copia_day = e3.created))
-> Merge Left Join (cost=533765.24..713430.14 rows=38826200 width=69) (actual time=23964.158..25581.680 rows=1223704 loops=1)
Merge Cond: ((d2.donor = r2.donor_id) AND (d2.copia_day = r2.created))
-> Sort (cost=336144.13..337793.13 rows=3298000 width=55) (actual time=17606.533..18760.805 rows=1223558 loops=1)
Sort Key: d2.donor, d2.copia_day
Sort Method: external merge Disk: 53152kB
-> Merge Left Join (cost=162012.51..184212.01 rows=3298000 width=55) (actual time=6416.760..16411.631 rows=1223558 loops=1)
Merge Cond: ((d2.donor = s2.donor_org) AND ((date_part('dow'::text, (d2.copia_day)::timestamp without time zone)) = ((s2.day_of_week)::double precision)))
Join Filter: ((d2.copia_day >= s2.start_date) AND ((d2.copia_day < s2.finish_date) OR (s2.finish_date IS NULL)))
Rows Removed by Join Filter: 17175126
-> Sort (cost=126985.25..128634.25 rows=3298000 width=14) (actual time=3179.606..5695.192 rows=1223558 loops=1)
Sort Key: d2.donor, (date_part('dow'::text, (d2.copia_day)::timestamp without time zone))
Sort Method: external merge Disk: 40776kB
-> Subquery Scan on d2 (cost=445.75..15313.13 rows=3298000 width=14) (actual time=7.377..856.565 rows=1223558 loops=1)
-> Subquery Scan on d1 (cost=445.75..5419.13 rows=3298000 width=14) (actual time=7.370..497.126 rows=1223558 loops=1)
-> HashAggregate (cost=445.75..455.64 rows=3298 width=18) (actual time=7.344..8.771 rows=3298 loops=1)
Group Key: e2.external_id
-> Sort (cost=410.39..414.81 rows=8839 width=427) (actual time=4.685..5.337 rows=8839 loops=1)
Sort Key: e2."timestamp" DESC
Sort Method: quicksort Memory: 1075kB
-> Seq Scan on donor_event e2 (cost=0.00..294.52 rows=8839 width=427) (actual time=0.007..2.464 rows=8839 loops=1)
-> Sort (cost=35027.26..35194.47 rows=334422 width=59) (actual time=3237.130..6188.442 rows=17730256 loops=1)
Sort Key: s2.donor_org, ((s2.day_of_week)::double precision)
Sort Method: external sort Disk: 28792kB
-> Subquery Scan on s2 (cost=24375.48..28890.18 rows=334422 width=59) (actual time=2305.941..2708.130 rows=334644 loops=1)
-> WindowAgg (cost=24375.48..27886.91 rows=334422 width=103) (actual time=2305.937..2659.268 rows=334644 loops=1)
-> Sort (cost=24375.48..24542.69 rows=334422 width=91) (actual time=2305.910..2400.087 rows=334644 loops=1)
Sort Key: ((s1.donor_org || (s1.day_of_week)::text)), s1.donor_org, s1.day_of_week, s1."timestamp"
Sort Method: external merge Disk: 28104kB
-> Seq Scan on schedule_event s1 (cost=0.00..5990.90 rows=334422 width=91) (actual time=0.020..112.939 rows=334644 loops=1)
-> Sort (cost=197621.11..197856.56 rows=470906 width=29) (actual time=6357.511..6427.190 rows=453368 loops=1)
Sort Key: r2.donor_id, r2.created
Sort Method: external sort Disk: 21224kB
-> Subquery Scan on r2 (cost=185426.99..188746.85 rows=470906 width=29) (actual time=5257.723..5763.028 rows=453368 loops=1)
-> Group (cost=185426.99..187334.13 rows=470906 width=29) (actual time=5257.722..5722.512 rows=453368 loops=1)
Group Key: r1.donor_id, r1.recipient_id, ((r1.created)::date)
-> Sort (cost=185426.99..185844.91 rows=835841 width=29) (actual time=5257.719..5579.364 rows=816699 loops=1)
Sort Key: r1.donor_id, r1.recipient_id, ((r1.created)::date)
Sort Method: external merge Disk: 34520kB
-> Seq Scan on donation_event r1 (cost=0.00..154698.60 rows=835841 width=29) (actual time=8.872..1038.453 rows=816699 loops=1)
Filter: (event_type = 'Accepted'::text)
Rows Removed by Filter: 5007071
-> Materialize (cost=1466353.37..1472469.28 rows=6115909 width=99) (actual time=56266.246..57390.089 rows=836733 loops=1)
-> Sort (cost=1466353.37..1469411.33 rows=6115909 width=99) (actual time=56266.242..57306.796 rows=836587 loops=1)
Sort Key: e3.donor_id, e3.created
Sort Method: external merge Disk: 80760kB
-> Subquery Scan on e3 (cost=918325.04..1089570.49 rows=6115909 width=99) (actual time=44865.491..51616.598 rows=836587 loops=1)
-> GroupAggregate (cost=918325.04..1071222.76 rows=6115909 width=99) (actual time=44865.489..51512.465 rows=836587 loops=1)
Group Key: ((e1.created)::date), e1.donor_id, (count(donation_event.aggregate_id) FILTER (WHERE ((count(DISTINCT donation_event.recipient_id)) > 1)))
-> Sort (cost=918325.04..921382.99 rows=6115909 width=41) (actual time=44865.429..46121.958 rows=5823770 loops=1)
Sort Key: ((e1.created)::date) DESC, e1.donor_id, (count(donation_event.aggregate_id) FILTER (WHERE ((count(DISTINCT donation_event.recipient_id)) > 1)))
Sort Method: external merge Disk: 318544kB
-> Merge Left Join (cost=620495.08..646062.16 rows=6115909 width=41) (actual time=27280.810..36782.345 rows=5823770 loops=1)
Merge Cond: ((((e1.created)::date) = ((donation_event.created)::date)) AND (e1.donor_id = donation_event.donor_id))
-> Sort (cost=393620.61..396678.56 rows=6115909 width=29) (actual time=21096.864..25693.924 rows=5823770 loops=1)
Sort Key: ((e1.created)::date), e1.donor_id
Sort Method: external merge Disk: 282832kB
-> Seq Scan on donation_event e1 (cost=0.00..151222.73 rows=6115909 width=29) (actual time=7.843..1592.371 rows=5823770 loops=1)
-> Materialize (cost=226874.47..235196.35 rows=40000 width=23) (actual time=6183.941..8439.538 rows=5390804 loops=1)
-> GroupAggregate (cost=226874.47..235056.35 rows=40000 width=23) (actual time=6183.935..8124.796 rows=639481 loops=1)
Group Key: ((donation_event.created)::date), donation_event.donor_id
-> GroupAggregate (cost=226874.47..232143.96 rows=558479 width=59) (actual time=6183.920..7844.628 rows=654647 loops=1)
Group Key: ((donation_event.created)::date), donation_event.donor_id, donation_event.aggregate_id
-> Sort (cost=226874.47..227537.43 rows=1325925 width=65) (actual time=6183.891..6583.376 rows=1260557 loops=1)
Sort Key: ((donation_event.created)::date), donation_event.donor_id, donation_event.aggregate_id
Sort Method: external merge Disk: 98624kB
-> Seq Scan on donation_event (cost=0.00..161059.55 rows=1325925 width=65) (actual time=8.769..1548.906 rows=1260557 loops=1)
Filter: (((recipient_id IS NOT NULL) OR (recipient_id <> ''::text)) AND (aggregate_id <> ''::text) AND (event_type = 'Offered'::text))
Rows Removed by Filter: 4563213
Planning time: 0.928 ms
Execution time: 83778.928 ms
Best Answer
The only thing that both stood out for me and was something I knew what to suggest about is your use of ORDER BYs without LIMITs in derived tables. You are using them on two occasions, once in the
e1derived table that is insided1insided2, and again insidee3. Those ORDER BYs are unnecessary and they are not optimised away, if I am reading the execution plan correctly. Not sure if removing them will have much of an impact but it is something you could start with.There are other places where I think you might be doing something in a suboptimal way, but I have no idea what to suggest as a replacement/rewrite without knowing much (or indeed anything) about what you are trying to do there.
And I would rather not ask you for more details because, in the end, the query is too big for this question to be of much help to other people. Please do not make any mistake: this site is about helping people with their problems, but the idea is that each contribution builds towards a knowledge base that can be used on many occasions without asking. Thus questions should, as much as possible, be useful not just to the original poster and whoever took the exercise of answering them but to the wider audience as well.
Therefore, I think, instead of asking people entirely unfamiliar with the schema, data or business logic to help you with a wall of code, you should consider a different approach. Try starting with a minimal subset of your query that is both working and fast. From that point on, gradually add other parts – like, one derived table at a time – until the query is no longer efficient enough for you.
That way you would be hitting a specific problem, which would let you come up with a specific question to the community. Not only would such a question be easier to analyse and answer, it would also have more chances to be generic enough for other people to find the answers useful for them as well, which would be fulfilling the goal of this site.