explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5DPm

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,840.475 ↑ 1.0 15 1

Append (cost=204,576.47..563,067.94 rows=15 width=44) (actual time=724.933..1,840.475 rows=15 loops=1)

2. 0.001 724.932 ↑ 1.0 5 1

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

3. 0.037 724.931 ↑ 20.0 5 1

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

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

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

  • Group Key: fact.account_sid
5. 7.231 719.221 ↑ 2.4 26,438 1

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

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

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

  • Hash Cond: (mi.checksum_sid = fact.checksum_sid)
7. 519.211 542.230 ↑ 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.421..542.230 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.019 23.019 ↑ 1.0 106,097 1

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

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

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

  • Buckets: 131072 Batches: 4 Memory Usage: 3451kB
10. 77.803 77.803 ↓ 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.138..77.803 rows=247,304 loops=1)

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

Limit (cost=183,828.02..183,828.03 rows=5 width=44) (actual time=644.680..644.682 rows=5 loops=1)

12. 0.035 644.679 ↑ 20.0 5 1

Sort (cost=183,828.02..183,828.27 rows=100 width=44) (actual time=644.678..644.679 rows=5 loops=1)

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

GroupAggregate (cost=183,574.94..183,826.36 rows=100 width=44) (actual time=640.329..644.644 rows=98 loops=1)

  • Group Key: fact_1.account_sid
14. 4.111 641.571 ↑ 2.4 14,093 1

Sort (cost=183,574.94..183,658.41 rows=33,389 width=8) (actual time=640.198..641.571 rows=14,093 loops=1)

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

Hash Join (cost=13,399.70..181,066.24 rows=33,389 width=8) (actual time=83.719..637.460 rows=14,093 loops=1)

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

Bitmap Heap Scan on email_inheritance ei (cost=2,250.61..161,337.27 rows=105,969 width=4) (actual time=28.291..550.315 rows=105,757 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: 3014798
  • Heap Blocks: exact=26042 lossy=53207
17. 23.282 23.282 ↑ 1.0 105,757 1

Bitmap Index Scan on combineddateei (cost=0.00..2,224.12 rows=105,969 width=0) (actual time=23.282..23.282 rows=105,757 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3664kB
19. 23.791 33.249 ↑ 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=9.618..33.249 rows=134,405 loops=1)

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

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

  • Index Cond: ((activity_type)::text = 'email'::text)
21. 0.002 470.856 ↑ 1.0 5 1

Limit (cost=174,663.26..174,663.28 rows=5 width=44) (actual time=470.855..470.856 rows=5 loops=1)

22. 0.030 470.854 ↑ 20.0 5 1

Sort (cost=174,663.26..174,663.51 rows=100 width=44) (actual time=470.853..470.854 rows=5 loops=1)

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

GroupAggregate (cost=174,448.70..174,661.60 rows=100 width=44) (actual time=467.046..470.824 rows=99 loops=1)

  • Group Key: fact_2.account_sid
24. 3.537 468.097 ↑ 2.2 12,724 1

Sort (cost=174,448.70..174,519.34 rows=28,253 width=8) (actual time=466.948..468.097 rows=12,724 loops=1)

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

Hash Join (cost=12,554.55..172,359.94 rows=28,253 width=8) (actual time=79.241..464.560 rows=12,724 loops=1)

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

Bitmap Heap Scan on phone_inheritance pi (cost=2,156.71..153,820.66 rows=101,491 width=4) (actual time=30.467..386.574 rows=105,873 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: 1604371
  • Heap Blocks: exact=51318 lossy=26947
27. 20.537 20.537 ↓ 1.0 105,873 1

Bitmap Index Scan on combineddatepi (cost=0.00..2,131.34 rows=101,491 width=0) (actual time=20.537..20.537 rows=105,873 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3363kB
29. 21.259 29.627 ↑ 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.507..29.627 rows=119,683 loops=1)

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

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

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