explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mVR9

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,713.757 ↑ 1.0 15 1

Append (cost=204,576.47..644,043.94 rows=15 width=44) (actual time=713.938..3,713.757 rows=15 loops=1)

2. 0.001 713.939 ↑ 1.0 5 1

Limit (cost=204,576.47..204,576.48 rows=5 width=44) (actual time=713.938..713.939 rows=5 loops=1)

3. 0.036 713.938 ↑ 20.0 5 1

Sort (cost=204,576.47..204,576.72 rows=100 width=44) (actual time=713.937..713.938 rows=5 loops=1)

  • Sort Key: (count(DISTINCT mi.checksum_sid))
  • Sort Method: top-N heapsort Memory: 25kB
4. 5.855 713.902 ↑ 1.0 99 1

GroupAggregate (cost=204,104.95..204,574.81 rows=100 width=44) (actual time=705.528..713.902 rows=99 loops=1)

  • Group Key: fact.account_sid
5. 7.510 708.047 ↑ 2.4 26,438 1

Sort (cost=204,104.95..204,261.24 rows=62,514 width=8) (actual time=705.480..708.047 rows=26,438 loops=1)

  • Sort Key: fact.account_sid
  • Sort Method: quicksort Memory: 2008kB
6. 47.159 700.537 ↑ 2.4 26,438 1

Hash Join (cost=17,314.78..199,125.12 rows=62,514 width=8) (actual time=147.482..700.537 rows=26,438 loops=1)

  • Hash Cond: (mi.checksum_sid = fact.checksum_sid)
7. 512.504 535.850 ↑ 1.0 106,097 1

Bitmap Heap Scan on meeting_inheritance mi (cost=2,325.38..174,585.11 rows=109,361 width=4) (actual time=28.645..535.850 rows=106,097 loops=1)

  • Recheck Cond: ((activity_date > 1544316837) AND (activity_date < 1549673664) AND (NOT is_blacklist) AND (NOT is_partner) AND (NOT is_internal))
  • Rows Removed by Index Recheck: 2701885
  • Heap Blocks: exact=28691 lossy=53298
8. 23.346 23.346 ↑ 1.0 106,097 1

Bitmap Index Scan on combineddatemi (cost=0.00..2,298.04 rows=109,361 width=0) (actual time=23.346..23.346 rows=106,097 loops=1)

  • Index Cond: ((activity_date > 1544316837) AND (activity_date < 1549673664))
9. 40.261 117.528 ↓ 1.0 247,304 1

Hash (cost=10,953.40..10,953.40 rows=246,000 width=8) (actual time=117.528..117.528 rows=247,304 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3451kB
10. 77.267 77.267 ↓ 1.0 247,304 1

Seq Scan on derived_activity_account_fact fact (cost=0.00..10,953.40 rows=246,000 width=8) (actual time=0.135..77.267 rows=247,304 loops=1)

  • Filter: ((activity_type)::text = 'meeting'::text)
  • Rows Removed by Filter: 254088
11. 0.003 1,462.755 ↑ 1.0 5 1

Limit (cost=225,275.15..225,275.16 rows=5 width=44) (actual time=1,462.754..1,462.755 rows=5 loops=1)

12. 0.018 1,462.752 ↑ 20.0 5 1

Sort (cost=225,275.15..225,275.40 rows=100 width=44) (actual time=1,462.752..1,462.752 rows=5 loops=1)

  • Sort Key: (count(DISTINCT ei.checksum_sid))
  • Sort Method: top-N heapsort Memory: 25kB
13. 3.050 1,462.734 ↑ 1.0 98 1

GroupAggregate (cost=225,022.07..225,273.48 rows=100 width=44) (actual time=1,458.421..1,462.734 rows=98 loops=1)

  • Group Key: fact_1.account_sid
14. 4.326 1,459.684 ↑ 2.4 14,093 1

Sort (cost=225,022.07..225,105.54 rows=33,389 width=8) (actual time=1,458.287..1,459.684 rows=14,093 loops=1)

  • Sort Key: fact_1.account_sid
  • Sort Method: quicksort Memory: 1045kB
15. 39.464 1,455.358 ↑ 2.4 14,093 1

Hash Join (cost=28,867.01..222,513.37 rows=33,389 width=8) (actual time=174.606..1,455.358 rows=14,093 loops=1)

  • Hash Cond: (ei.checksum_sid = fact_1.checksum_sid)
16. 1,250.604 1,359.366 ↑ 1.0 105,757 1

Bitmap Heap Scan on email_inheritance ei (cost=17,717.92..202,784.40 rows=105,969 width=4) (actual time=116.851..1,359.366 rows=105,757 loops=1)

  • Recheck Cond: ((activity_date > 1544316837) AND (activity_date < 1549673664))
  • Rows Removed by Index Recheck: 6990764
  • Filter: ((NOT is_internal) AND (NOT is_blacklist) AND (NOT is_partner))
  • Rows Removed by Filter: 741478
  • Heap Blocks: exact=39209 lossy=132223
17. 108.762 108.762 ↓ 1.0 847,235 1

Bitmap Index Scan on activitydateeh (cost=0.00..17,691.42 rows=843,499 width=0) (actual time=108.762..108.762 rows=847,235 loops=1)

  • Index Cond: ((activity_date > 1544316837) AND (activity_date < 1549673664))
18. 22.740 56.528 ↑ 1.0 134,405 1

Hash (cost=8,924.18..8,924.18 rows=135,593 width=8) (actual time=56.528..56.528 rows=134,405 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3664kB
19. 23.731 33.788 ↑ 1.0 134,405 1

Bitmap Heap Scan on derived_activity_account_fact fact_1 (cost=2,543.27..8,924.18 rows=135,593 width=8) (actual time=10.223..33.788 rows=134,405 loops=1)

  • Recheck Cond: ((activity_type)::text = 'email'::text)
  • Heap Blocks: exact=1364
20. 10.057 10.057 ↑ 1.0 134,405 1

Bitmap Index Scan on activitytypeindexfact (cost=0.00..2,509.37 rows=135,593 width=0) (actual time=10.057..10.057 rows=134,405 loops=1)

  • Index Cond: ((activity_type)::text = 'email'::text)
21. 0.001 1,537.060 ↑ 1.0 5 1

Limit (cost=214,192.14..214,192.15 rows=5 width=44) (actual time=1,537.059..1,537.060 rows=5 loops=1)

22. 0.027 1,537.059 ↑ 20.0 5 1

Sort (cost=214,192.14..214,192.39 rows=100 width=44) (actual time=1,537.058..1,537.059 rows=5 loops=1)

  • Sort Key: (count(DISTINCT pi.checksum_sid))
  • Sort Method: top-N heapsort Memory: 25kB
23. 2.672 1,537.032 ↑ 1.0 99 1

GroupAggregate (cost=213,977.58..214,190.48 rows=100 width=44) (actual time=1,533.211..1,537.032 rows=99 loops=1)

  • Group Key: fact_2.account_sid
24. 3.844 1,534.360 ↑ 2.2 12,724 1

Sort (cost=213,977.58..214,048.21 rows=28,253 width=8) (actual time=1,533.114..1,534.360 rows=12,724 loops=1)

  • Sort Key: fact_2.account_sid
  • Sort Method: quicksort Memory: 981kB
25. 34.467 1,530.516 ↑ 2.2 12,724 1

Hash Join (cost=27,557.54..211,888.82 rows=28,253 width=8) (actual time=161.180..1,530.516 rows=12,724 loops=1)

  • Hash Cond: (pi.checksum_sid = fact_2.checksum_sid)
26. 1,342.030 1,449.443 ↓ 1.0 105,873 1

Bitmap Heap Scan on phone_inheritance pi (cost=17,159.70..193,349.53 rows=101,491 width=4) (actual time=114.284..1,449.443 rows=105,873 loops=1)

  • Recheck Cond: ((activity_date > 1544316837) AND (activity_date < 1549673664))
  • Rows Removed by Index Recheck: 7360732
  • Filter: ((NOT is_internal) AND (NOT is_blacklist) AND (NOT is_partner))
  • Rows Removed by Filter: 742032
  • Heap Blocks: exact=30997 lossy=132135
27. 107.413 107.413 ↓ 1.0 847,905 1

Bitmap Index Scan on activitydatepi (cost=0.00..17,134.33 rows=816,989 width=0) (actual time=107.413..107.413 rows=847,905 loops=1)

  • Index Cond: ((activity_date > 1544316837) AND (activity_date < 1549673664))
28. 17.187 46.606 ↑ 1.0 119,683 1

Hash (cost=8,432.35..8,432.35 rows=119,799 width=8) (actual time=46.606..46.606 rows=119,683 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3363kB
29. 21.188 29.419 ↑ 1.0 119,683 1

Bitmap Heap Scan on derived_activity_account_fact fact_2 (cost=2,248.86..8,432.35 rows=119,799 width=8) (actual time=8.378..29.419 rows=119,683 loops=1)

  • Recheck Cond: ((activity_type)::text = 'phone'::text)
  • Heap Blocks: exact=1212
30. 8.231 8.231 ↑ 1.0 119,683 1

Bitmap Index Scan on activitytypeindexfact (cost=0.00..2,218.91 rows=119,799 width=0) (actual time=8.231..8.231 rows=119,683 loops=1)

  • Index Cond: ((activity_type)::text = 'phone'::text)