Optimizing Very Large Query (200+ Lines with 5 Million Rows) & no write access to database

Question

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

Answer 1

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 e1 derived table that is inside d1 inside d2, and again inside e3. 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.