explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qphI

Settings
# exclusive inclusive rows x rows loops node
1. 8.115 158,576.395 ↓ 1.1 865 1

Hash Right Join (cost=2,222,095.34..2,222,165.30 rows=807 width=243) (actual time=157,728.841..158,576.395 rows=865 loops=1)

  • Hash Cond: ((afterdate.account)::text = (beforedate.account)::text)
2.          

CTE beforedate

3. 2.408 80,280.733 ↓ 1.4 999 1

Finalize GroupAggregate (cost=1,110,124.50..1,111,027.63 rows=696 width=57) (actual time=79,456.990..80,280.733 rows=999 loops=1)

  • Group Key: flag_evaluations.account
4. 0.000 80,278.325 ↓ 2.0 2,717 1

Gather Merge (cost=1,110,124.50..1,111,008.49 rows=1,392 width=57) (actual time=79,456.751..80,278.325 rows=2,717 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 855.642 240,059.856 ↓ 1.3 906 3

Partial GroupAggregate (cost=1,109,124.47..1,109,847.79 rows=696 width=57) (actual time=79,426.999..80,019.952 rows=906 loops=3)

  • Group Key: flag_evaluations.account
6. 4,754.826 239,204.214 ↓ 4.9 463,027 3

Sort (cost=1,109,124.47..1,109,362.68 rows=95,283 width=33) (actual time=79,426.945..79,734.738 rows=463,027 loops=3)

  • Sort Key: flag_evaluations.account
  • Sort Method: external merge Disk: 23984kB
7. 234,449.388 234,449.388 ↓ 4.9 463,027 3

Parallel Seq Scan on flag_evaluations (cost=0.00..1,098,637.10 rows=95,283 width=33) (actual time=75,043.067..78,149.796 rows=463,027 loops=3)

  • Filter: (date_trunc('week'::text, "time") = '2019-06-03 00:00:00-07'::timestamp with time zone)
  • Rows Removed by Filter: 14782312
8.          

CTE afterdate

9. 2.472 78,284.871 ↓ 1.4 999 1

Finalize GroupAggregate (cost=1,110,124.50..1,111,027.63 rows=696 width=57) (actual time=77,446.144..78,284.871 rows=999 loops=1)

  • Group Key: flag_evaluations_1.account
10. 0.000 78,282.399 ↓ 2.0 2,729 1

Gather Merge (cost=1,110,124.50..1,111,008.49 rows=1,392 width=57) (actual time=77,445.967..78,282.399 rows=2,729 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 974.298 234,369.912 ↓ 1.3 910 3

Partial GroupAggregate (cost=1,109,124.47..1,109,847.79 rows=696 width=57) (actual time=77,434.899..78,123.304 rows=910 loops=3)

  • Group Key: flag_evaluations_1.account
12. 4,618.797 233,395.614 ↓ 4.7 450,179 3

Sort (cost=1,109,124.47..1,109,362.68 rows=95,283 width=33) (actual time=77,434.830..77,798.538 rows=450,179 loops=3)

  • Sort Key: flag_evaluations_1.account
  • Sort Method: external merge Disk: 22792kB
13. 228,776.817 228,776.817 ↓ 4.7 450,179 3

Parallel Seq Scan on flag_evaluations flag_evaluations_1 (cost=0.00..1,098,637.10 rows=95,283 width=33) (actual time=73,843.794..76,258.939 rows=450,179 loops=3)

  • Filter: (date_trunc('week'::text, "time") = '2019-06-10 00:00:00-07'::timestamp with time zone)
  • Rows Removed by Filter: 14795160
14. 78,285.618 78,285.618 ↓ 1.4 999 1

CTE Scan on afterdate (cost=0.00..13.92 rows=696 width=98) (actual time=77,446.149..78,285.618 rows=999 loops=1)

15. 0.425 80,282.662 ↓ 3.7 865 1

Hash (cost=37.18..37.18 rows=232 width=170) (actual time=80,282.662..80,282.662 rows=865 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
16. 0.593 80,282.237 ↓ 3.7 865 1

Hash Left Join (cost=20.64..37.18 rows=232 width=170) (actual time=79,457.038..80,282.237 rows=865 loops=1)

  • Hash Cond: ((beforedate.account)::text = c.ld_account_id__c)
17. 80,281.621 80,281.621 ↓ 3.7 865 1

CTE Scan on beforedate (cost=0.00..15.66 rows=232 width=98) (actual time=79,456.998..80,281.621 rows=865 loops=1)

  • Filter: (evals >= '100'::numeric)
  • Rows Removed by Filter: 134
18. 0.002 0.023 ↓ 0.0 0 1

Hash (cost=20.63..20.63 rows=1 width=104) (actual time=0.023..0.023 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.001 0.021 ↓ 0.0 0 1

Subquery Scan on c (cost=20.59..20.63 rows=1 width=104) (actual time=0.021..0.021 rows=0 loops=1)

20. 0.002 0.020 ↓ 0.0 0 1

GroupAggregate (cost=20.59..20.62 rows=1 width=1,126) (actual time=0.020..0.020 rows=0 loops=1)

  • Group Key: sfdc_account.sfid
21.          

CTE last_import

22. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=10.25..10.26 rows=1 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on sfdc_account sfdc_account_1 (cost=0.00..10.20 rows=20 width=8) (never executed)

24. 0.011 0.018 ↓ 0.0 0 1

Sort (cost=10.33..10.33 rows=1 width=158) (actual time=0.018..0.018 rows=0 loops=1)

  • Sort Key: sfdc_account.sfid
  • Sort Method: quicksort Memory: 25kB
25. 0.007 0.007 ↓ 0.0 0 1

Hash Join (cost=0.03..10.32 rows=1 width=158) (actual time=0.006..0.007 rows=0 loops=1)

  • Hash Cond: (sfdc_account.imported_at = last_import."time")
  • -> Seq Scan on sfdc_account (cost=0.00..10.20 rows=20 width=166) (actual time=0.003..0.003 rows=0 loops=1)..0.003 rows=0 loops=1)
26. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on last_import (cost=0.00..0.02 rows=1 width=8) (never executed)

Planning time : 23.472 ms
Execution time : 158,589.928 ms