explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HEo1

Settings
# exclusive inclusive rows x rows loops node
1. 11,865.769 346,927.044 ↑ 12.6 298,142 1

GroupAggregate (cost=11,032,396.67..11,116,628.55 rows=3,743,639 width=547) (actual time=270,514.609..346,927.044 rows=298,142 loops=1)

  • Group Key: dps.email, internal.email
2.          

CTE internal

3. 126.560 13,337.165 ↑ 3.8 19,167 1

Unique (cost=2,042,764.89..2,043,133.68 rows=73,758 width=78) (actual time=12,987.024..13,337.165 rows=19,167 loops=1)

4. 1,745.553 13,210.605 ↓ 20.7 1,528,511 1

Sort (cost=2,042,764.89..2,042,949.28 rows=73,758 width=78) (actual time=12,987.022..13,210.605 rows=1,528,511 loops=1)

  • Sort Key: fd_1.lead_id, dps_1.email, fd_1.created_at
  • Sort Method: external merge Disk: 104,432kB
5. 442.031 11,465.052 ↓ 20.7 1,528,511 1

Hash Left Join (cost=285,937.78..2,033,521.87 rows=73,758 width=78) (actual time=1,070.044..11,465.052 rows=1,528,511 loops=1)

  • Hash Cond: (fd_1.affiliate_id = da_1.id)
6. 747.511 11,022.472 ↓ 20.7 1,528,511 1

Nested Loop (cost=285,906.99..2,033,112.25 rows=73,758 width=45) (actual time=1,069.473..11,022.472 rows=1,528,511 loops=1)

7. 2,290.495 7,212.455 ↓ 13.0 1,531,253 1

Hash Join (cost=285,906.56..1,976,340.77 rows=118,240 width=26) (actual time=1,069.449..7,212.455 rows=1,531,253 loops=1)

  • Hash Cond: (fd_1.lead_id = dl_1.source_id)
8. 377.098 3,853.788 ↓ 1.2 5,794,455 1

Append (cost=0.14..1,617,905.71 rows=4,950,721 width=24) (actual time=0.062..3,853.788 rows=5,794,455 loops=1)

  • Subplans Removed: 8
9. 3,476.690 3,476.690 ↓ 1.2 5,794,455 1

Index Scan using facts_distribution_test_2020_05_01_created_at_idx on facts_distribution_test_2020_05_01 fd_1 (cost=0.58..1,593,086.48 rows=4,950,713 width=24) (actual time=0.061..3,476.690 rows=5,794,455 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()) - '7 days'::interval))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()) - '00:00:00'::interval))::date))
  • Filter: ((type)::text = 'post'::text)
  • Rows Removed by Filter: 1,645,323
10. 96.642 1,068.172 ↓ 1.0 311,558 1

Hash (cost=280,514.35..280,514.35 rows=310,165 width=10) (actual time=1,068.172..1,068.172 rows=311,558 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,846kB
11. 0.000 971.530 ↓ 1.0 311,558 1

Gather (cost=1,000.00..280,514.35 rows=310,165 width=10) (actual time=0.514..971.530 rows=311,558 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 982.513 982.513 ↑ 1.2 103,853 3 / 3

Parallel Seq Scan on dim_lead dl_1 (cost=0.00..248,497.85 rows=129,235 width=10) (actual time=0.020..982.513 rows=103,853 loops=3)

  • Filter: (vertical = ANY ('{41,50,57}'::integer[]))
  • Rows Removed by Filter: 4,240,145
13. 3,062.506 3,062.506 ↑ 1.0 1 1,531,253

Index Scan using dim_person_pkey on dim_person dps_1 (cost=0.43..0.48 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=1,531,253)

  • Index Cond: (id = fd_1.person_id)
14. 0.313 0.549 ↑ 1.0 1,013 1

Hash (cost=18.13..18.13 rows=1,013 width=15) (actual time=0.549..0.549 rows=1,013 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
15. 0.236 0.236 ↑ 1.0 1,013 1

Seq Scan on dim_affiliate da_1 (cost=0.00..18.13 rows=1,013 width=15) (actual time=0.009..0.236 rows=1,013 loops=1)

16.          

CTE posted

17. 485.705 22,192.980 ↑ 1.1 840,929 1

Unique (cost=2,676,935.40..2,684,835.68 rows=888,725 width=78) (actual time=20,925.503..22,192.980 rows=840,929 loops=1)

18. 6,185.321 21,707.275 ↓ 2.7 4,231,896 1

Sort (cost=2,676,935.40..2,680,885.54 rows=1,580,057 width=78) (actual time=20,925.502..21,707.275 rows=4,231,896 loops=1)

  • Sort Key: fd_2.lead_id, dps_2.email, fd_2.created_at
  • Sort Method: external merge Disk: 277,144kB
19. 937.904 15,521.954 ↓ 2.7 4,231,896 1

Hash Left Join (cost=579,421.02..2,373,836.32 rows=1,580,057 width=78) (actual time=5,042.627..15,521.954 rows=4,231,896 loops=1)

  • Hash Cond: (fd_2.affiliate_id = da_2.id)
20. 2,619.205 14,583.760 ↓ 2.7 4,231,896 1

Hash Join (cost=579,390.23..2,365,690.45 rows=1,580,057 width=45) (actual time=5,042.314..14,583.760 rows=4,231,896 loops=1)

  • Hash Cond: (fd_2.person_id = dps_2.id)
21. 3,105.234 10,644.795 ↓ 1.7 4,262,741 1

Hash Join (cost=451,927.87..2,173,291.08 rows=2,532,954 width=26) (actual time=3,720.903..10,644.795 rows=4,262,741 loops=1)

  • Hash Cond: (fd_2.lead_id = dl_2.source_id)
22. 454.041 3,821.553 ↓ 1.2 5,794,455 1

Append (cost=0.14..1,617,905.71 rows=4,950,721 width=24) (actual time=0.076..3,821.553 rows=5,794,455 loops=1)

  • Subplans Removed: 8
23. 3,367.512 3,367.512 ↓ 1.2 5,794,455 1

Index Scan using facts_distribution_test_2020_05_01_created_at_idx on facts_distribution_test_2020_05_01 fd_2 (cost=0.58..1,593,086.48 rows=4,950,713 width=24) (actual time=0.075..3,367.512 rows=5,794,455 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()) - '7 days'::interval))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()) - '00:00:00'::interval))::date))
  • Filter: ((type)::text = 'post'::text)
  • Rows Removed by Filter: 1,645,323
24. 1,309.319 3,718.008 ↓ 1.0 6,761,220 1

Hash (cost=336,428.50..336,428.50 rows=6,644,418 width=10) (actual time=3,718.008..3,718.008 rows=6,761,220 loops=1)

  • Buckets: 131,072 Batches: 128 Memory Usage: 3,502kB
25. 2,408.689 2,408.689 ↓ 1.0 6,761,220 1

Seq Scan on dim_lead dl_2 (cost=0.00..336,428.50 rows=6,644,418 width=10) (actual time=0.011..2,408.689 rows=6,761,220 loops=1)

  • Filter: (vertical = 6)
  • Rows Removed by Filter: 6,270,773
26. 807.961 1,319.760 ↑ 1.0 3,458,147 1

Hash (cost=60,550.05..60,550.05 rows=3,460,505 width=27) (actual time=1,319.760..1,319.760 rows=3,458,147 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 3,726kB
27. 511.799 511.799 ↑ 1.0 3,458,147 1

Seq Scan on dim_person dps_2 (cost=0.00..60,550.05 rows=3,460,505 width=27) (actual time=0.010..511.799 rows=3,458,147 loops=1)

28. 0.158 0.290 ↑ 1.0 1,013 1

Hash (cost=18.13..18.13 rows=1,013 width=15) (actual time=0.290..0.290 rows=1,013 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
29. 0.132 0.132 ↑ 1.0 1,013 1

Seq Scan on dim_affiliate da_2 (cost=0.00..18.13 rows=1,013 width=15) (actual time=0.007..0.132 rows=1,013 loops=1)

30. 252,129.023 335,061.275 ↓ 18.5 69,319,960 1

Sort (cost=6,304,427.31..6,313,786.40 rows=3,743,639 width=547) (actual time=270,514.571..335,061.275 rows=69,319,960 loops=1)

  • Sort Key: dps.email, internal.email
  • Sort Method: external merge Disk: 3,818,936kB
31. 12,554.542 82,932.252 ↓ 18.5 69,319,960 1

Hash Right Join (cost=3,022,180.53..3,131,834.12 rows=3,743,639 width=547) (actual time=70,025.401..82,932.252 rows=69,319,960 loops=1)

  • Hash Cond: ((internal.email)::text = (dps.email)::text)
32. 13,342.429 13,342.429 ↑ 3.8 19,167 1

CTE Scan on internal (cost=0.00..1,475.16 rows=73,758 width=524) (actual time=12,987.030..13,342.429 rows=19,167 loops=1)

33. 7,783.398 57,035.281 ↓ 9.9 37,098,506 1

Hash (cost=2,953,449.05..2,953,449.05 rows=3,743,639 width=23) (actual time=57,035.281..57,035.281 rows=37,098,506 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 128 (originally 64) Memory Usage: 31,378kB
34. 8,985.615 49,251.883 ↓ 9.9 37,098,506 1

Hash Right Join (cost=2,402,840.19..2,953,449.05 rows=3,743,639 width=23) (actual time=38,938.588..49,251.883 rows=37,098,506 loops=1)

  • Hash Cond: ((posted.email)::text = (dps.email)::text)
35. 22,463.698 22,463.698 ↑ 1.1 840,929 1

CTE Scan on posted (cost=0.00..17,774.50 rows=888,725 width=516) (actual time=20,925.508..22,463.698 rows=840,929 loops=1)

36. 1,316.434 17,802.570 ↓ 3.5 5,757,480 1

Hash (cost=2,372,592.75..2,372,592.75 rows=1,647,475 width=23) (actual time=17,802.569..17,802.570 rows=5,757,480 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 128 (originally 32) Memory Usage: 3,585kB
37. 1,138.000 16,486.136 ↓ 3.5 5,757,480 1

Hash Join (cost=610,487.80..2,372,592.75 rows=1,647,475 width=23) (actual time=5,424.804..16,486.136 rows=5,757,480 loops=1)

  • Hash Cond: (fd.affiliate_id = da.id)
38. 2,755.611 15,347.890 ↓ 3.5 5,760,407 1

Hash Join (cost=610,457.01..2,368,202.60 rows=1,653,815 width=27) (actual time=5,424.541..15,347.890 rows=5,760,407 loops=1)

  • Hash Cond: (fd.person_id = dps.id)
39. 3,393.645 11,329.231 ↓ 2.2 5,793,994 1

Hash Join (cost=482,994.64..2,189,410.86 rows=2,651,194 width=8) (actual time=4,159.018..11,329.231 rows=5,793,994 loops=1)

  • Hash Cond: (fd.lead_id = dl.source_id)
40. 442.087 3,778.673 ↓ 1.2 5,794,455 1

Append (cost=0.14..1,617,905.71 rows=4,950,721 width=16) (actual time=0.071..3,778.673 rows=5,794,455 loops=1)

  • Subplans Removed: 8
41. 3,336.586 3,336.586 ↓ 1.2 5,794,455 1

Index Scan using facts_distribution_test_2020_05_01_created_at_idx on facts_distribution_test_2020_05_01 fd (cost=0.58..1,593,086.48 rows=4,950,713 width=16) (actual time=0.070..3,336.586 rows=5,794,455 loops=1)

  • Index Cond: ((created_at >= ((timezone('Canada/Eastern'::text, now()) - '7 days'::interval))::date) AND (created_at <= ((timezone('Canada/Eastern'::text, now()) - '00:00:00'::interval))::date))
  • Filter: ((type)::text = 'post'::text)
  • Rows Removed by Filter: 1,645,323
42. 1,250.059 4,156.913 ↓ 1.0 7,072,778 1

Hash (cost=368,895.20..368,895.20 rows=6,954,584 width=8) (actual time=4,156.913..4,156.913 rows=7,072,778 loops=1)

  • Buckets: 131,072 Batches: 128 Memory Usage: 3,185kB
43. 2,906.854 2,906.854 ↓ 1.0 7,072,778 1

Seq Scan on dim_lead dl (cost=0.00..368,895.20 rows=6,954,584 width=8) (actual time=0.012..2,906.854 rows=7,072,778 loops=1)

  • Filter: (vertical = ANY ('{41,50,57,6}'::integer[]))
  • Rows Removed by Filter: 5,959,215
44. 777.498 1,263.048 ↑ 1.0 3,458,147 1

Hash (cost=60,550.05..60,550.05 rows=3,460,505 width=27) (actual time=1,263.048..1,263.048 rows=3,458,147 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 3,726kB
45. 485.550 485.550 ↑ 1.0 3,458,147 1

Seq Scan on dim_person dps (cost=0.00..60,550.05 rows=3,460,505 width=27) (actual time=0.004..485.550 rows=3,458,147 loops=1)

46. 0.133 0.246 ↑ 1.0 1,013 1

Hash (cost=18.13..18.13 rows=1,013 width=8) (actual time=0.246..0.246 rows=1,013 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
47. 0.113 0.113 ↑ 1.0 1,013 1

Seq Scan on dim_affiliate da (cost=0.00..18.13 rows=1,013 width=8) (actual time=0.008..0.113 rows=1,013 loops=1)

Planning time : 6.925 ms
Execution time : 347,566.177 ms