explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VbcP

Settings
# exclusive inclusive rows x rows loops node
1. 30,633.900 1,846,778.302 ↓ 51.6 79,439,758 1

Append (cost=107,092.17..711,380.55 rows=1,538,529 width=134) (actual time=1,480,397.091..1,846,778.302 rows=79,439,758 loops=1)

2. 33,065.284 1,666,098.194 ↓ 437,056.7 77,796,090 1

Subquery Scan on "*SELECT* 1" (cost=107,092.17..107,099.74 rows=178 width=112) (actual time=1,480,397.090..1,666,098.194 rows=77,796,090 loops=1)

3. 125,705.028 1,633,032.910 ↓ 437,056.7 77,796,090 1

GroupAggregate (cost=107,092.17..107,097.96 rows=178 width=129) (actual time=1,480,397.088..1,633,032.910 rows=77,796,090 loops=1)

  • Group Key: cmf.private_company_id, te.id, cnf.first_name, cnf.last_name
4. 121,401.175 1,507,327.882 ↓ 439,578.5 78,244,969 1

Sort (cost=107,092.17..107,092.62 rows=178 width=35) (actual time=1,480,397.058..1,507,327.882 rows=78,244,969 loops=1)

  • Sort Key: cmf.private_company_id, te.id, cnf.first_name, cnf.last_name
  • Sort Method: external merge Disk: 3,736,280kB
5. 233,714.791 1,385,926.707 ↓ 439,578.5 78,244,969 1

Nested Loop (cost=101,924.08..107,085.52 rows=178 width=35) (actual time=35,640.577..1,385,926.707 rows=78,244,969 loops=1)

6. 465,787.414 501,475.486 ↓ 2,711,401.8 650,736,430 1

Hash Join (cost=101,923.66..106,214.83 rows=240 width=35) (actual time=35,640.423..501,475.486 rows=650,736,430 loops=1)

  • Hash Cond: (lower(cnf.email1) = lower(ce.email_address))
7. 48.605 48.605 ↑ 1.0 83,349 1

Seq Scan on contact_fields cnf (cost=0.00..3,762.71 rows=84,171 width=39) (actual time=0.006..48.605 rows=83,349 loops=1)

8. 2,834.039 35,639.467 ↓ 191.7 2,633,676 1

Hash (cost=101,751.91..101,751.91 rows=13,740 width=72) (actual time=35,639.466..35,639.467 rows=2,633,676 loops=1)

  • Buckets: 65,536 (originally 16384) Batches: 131,072 (originally 1) Memory Usage: 31,442kB
9. 2,688.542 32,805.428 ↓ 191.7 2,633,676 1

Nested Loop (cost=38.27..101,751.91 rows=13,740 width=72) (actual time=2.535..32,805.428 rows=2,633,676 loops=1)

10. 2,377.671 16,320.333 ↓ 267.1 4,598,851 1

Nested Loop (cost=37.84..93,942.27 rows=17,218 width=22) (actual time=0.214..16,320.333 rows=4,598,851 loops=1)

11. 742.084 3,934.464 ↓ 190.5 1,668,033 1

Nested Loop (cost=37.28..86,265.26 rows=8,758 width=18) (actual time=0.167..3,934.464 rows=1,668,033 loops=1)

12. 1.275 12.144 ↑ 1.1 482 1

Nested Loop (cost=0.56..4,551.29 rows=537 width=10) (actual time=0.099..12.144 rows=482 loops=1)

13. 0.349 0.349 ↑ 1.0 526 1

Seq Scan on users u (cost=0.00..70.26 rows=526 width=25) (actual time=0.007..0.349 rows=526 loops=1)

14. 10.520 10.520 ↑ 1.0 1 526

Index Scan using tracked_email_addresses_lower_idx on tracked_email_addresses tea (cost=0.56..8.51 rows=1 width=58) (actual time=0.019..0.020 rows=1 loops=526)

  • Index Cond: (lower(email_address) = lower(u.email))
  • Filter: (email_address <> ''::text)
15. 2,900.194 3,180.236 ↑ 1.3 3,461 482

Bitmap Heap Scan on tracked_emails te (cost=36.72..107.59 rows=4,458 width=20) (actual time=0.771..6.598 rows=3,461 loops=482)

  • Recheck Cond: ((from_env = tea.id) OR (from_hdr = tea.id))
  • Rows Removed by Index Recheck: 5,560
  • Heap Blocks: exact=673,749 lossy=66,482
16. 0.964 280.042 ↓ 0.0 0 482

BitmapOr (cost=36.72..36.72 rows=4,458 width=0) (actual time=0.581..0.581 rows=0 loops=482)

17. 179.786 179.786 ↓ 1.2 3,410 482

Bitmap Index Scan on tracked_emails_from_env_idx (cost=0.00..22.47 rows=2,926 width=0) (actual time=0.373..0.373 rows=3,410 loops=482)

  • Index Cond: (from_env = tea.id)
18. 99.292 99.292 ↓ 2.2 3,430 482

Bitmap Index Scan on tracked_emails_from_hdr_idx (cost=0.00..12.02 rows=1,532 width=0) (actual time=0.206..0.206 rows=3,430 loops=482)

  • Index Cond: (from_hdr = tea.id)
19. 10,008.198 10,008.198 ↑ 3.0 3 1,668,033

Index Only Scan using tracked_emails_x_teas_entity1_id_entity2_id_idx on tracked_emails_x_teas tet (cost=0.56..0.79 rows=9 width=8) (actual time=0.003..0.006 rows=3 loops=1,668,033)

  • Index Cond: (entity1_id = te.id)
  • Heap Fetches: 4,598,851
20. 13,796.553 13,796.553 ↑ 1.0 1 4,598,851

Index Scan using tracked_email_addresses_pkey on tracked_email_addresses ce (cost=0.43..0.45 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=4,598,851)

  • Index Cond: (id = tet.entity2_id)
  • Filter: (email_address !~~* '%coatue%'::text)
  • Rows Removed by Filter: 0
21. 650,736.430 650,736.430 ↓ 0.0 0 650,736,430

Index Scan using company_fields_pkey on company_fields cmf (cost=0.43..3.63 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=650,736,430)

  • Index Cond: (id = cnf.employer_company_id)
  • Filter: (private_company_id IS NOT NULL)
  • Rows Removed by Filter: 0
22. 731.646 150,046.208 ↓ 1.1 1,643,668 1

Subquery Scan on "*SELECT* 2" (cost=542,745.89..596,588.17 rows=1,538,351 width=134) (actual time=145,610.485..150,046.208 rows=1,643,668 loops=1)

23. 2,854.316 149,314.562 ↓ 1.1 1,643,668 1

GroupAggregate (cost=542,745.89..581,204.66 rows=1,538,351 width=138) (actual time=145,610.483..149,314.562 rows=1,643,668 loops=1)

  • Group Key: cmf_1.private_company_id, te_1.id, ce_1.email_address
24. 4,870.858 146,460.246 ↓ 1.3 1,972,533 1

Sort (cost=542,745.89..546,591.76 rows=1,538,351 width=76) (actual time=145,610.457..146,460.246 rows=1,972,533 loops=1)

  • Sort Key: cmf_1.private_company_id, te_1.id, ce_1.email_address
  • Sort Method: external merge Disk: 112,416kB
25. 510.709 141,589.388 ↓ 1.3 1,972,533 1

Gather (cost=238,391.83..247,947.60 rows=1,538,351 width=76) (actual time=137,014.871..141,589.388 rows=1,972,533 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
26. 1,676.230 141,078.679 ↓ 1.1 328,756 6 / 6

Merge Join (cost=238,381.83..246,399.25 rows=307,670 width=76) (actual time=137,618.883..141,078.679 rows=328,756 loops=6)

  • Merge Cond: ((COALESCE(regexp_replace(cmf_1.website, '^(https?://)?(www\.)?'::text, ''::text))) = (""substring""(ce_1.email_address, '@(.*)$'::text)))
27. 871.555 4,443.732 ↑ 1.6 114,155 6 / 6

Sort (cost=127,858.02..128,324.05 rows=186,412 width=30) (actual time=4,238.995..4,443.732 rows=114,155 loops=6)

  • Sort Key: (COALESCE(regexp_replace(cmf_1.website, '^(https?://)?(www\.)?'::text, ''::text)))
  • Sort Method: external merge Disk: 6,568kB
  • Worker 0: Sort Method: external merge Disk: 6,696kB
  • Worker 1: Sort Method: external merge Disk: 6,760kB
  • Worker 2: Sort Method: external merge Disk: 6,736kB
  • Worker 3: Sort Method: external merge Disk: 7,616kB
  • Worker 4: Sort Method: external merge Disk: 6,680kB
28. 3,572.177 3,572.177 ↑ 1.2 155,497 6 / 6

Parallel Seq Scan on company_fields cmf_1 (cost=0.00..107,076.89 rows=186,412 width=30) (actual time=3.356..3,572.177 rows=155,497 loops=6)

  • Filter: (private_company_id IS NOT NULL)
  • Rows Removed by Filter: 54,078
29. 10,869.216 134,958.717 ↓ 179.9 3,050,935 6 / 6

Sort (cost=110,523.81..110,566.21 rows=16,958 width=72) (actual time=133,379.555..134,958.717 rows=3,050,935 loops=6)

  • Sort Key: (""substring""(ce_1.email_address, '@(.*)$'::text))
  • Sort Method: external sort Disk: 202,704kB
  • Worker 0: Sort Method: external sort Disk: 202,704kB
  • Worker 1: Sort Method: external sort Disk: 202,704kB
  • Worker 2: Sort Method: external sort Disk: 202,704kB
  • Worker 3: Sort Method: external sort Disk: 202,704kB
  • Worker 4: Sort Method: external sort Disk: 202,704kB
30. 24,361.859 124,089.501 ↓ 177.6 3,011,745 6 / 6

Nested Loop Anti Join (cost=38.69..109,332.54 rows=16,958 width=72) (actual time=0.424..124,089.501 rows=3,011,745 loops=6)

31. 9,990.228 49,140.281 ↓ 267.1 4,598,851 6 / 6

Nested Loop (cost=38.27..101,708.86 rows=17,218 width=72) (actual time=0.199..49,140.281 rows=4,598,851 loops=6)

32. 6,587.426 25,353.500 ↓ 267.1 4,598,851 6 / 6

Nested Loop (cost=37.84..93,942.27 rows=17,218 width=22) (actual time=0.177..25,353.500 rows=4,598,851 loops=6)

33. 1,433.251 5,421.810 ↓ 190.5 1,668,033 6 / 6

Nested Loop (cost=37.28..86,265.26 rows=8,758 width=18) (actual time=0.150..5,421.810 rows=1,668,033 loops=6)

34. 2.309 21.217 ↑ 1.1 482 6 / 6

Nested Loop (cost=0.56..4,551.29 rows=537 width=10) (actual time=0.105..21.217 rows=482 loops=6)

35. 1.024 1.024 ↑ 1.0 526 6 / 6

Seq Scan on users u_1 (cost=0.00..70.26 rows=526 width=25) (actual time=0.017..1.024 rows=526 loops=6)

36. 17.884 17.884 ↑ 1.0 1 3,156 / 6

Index Scan using tracked_email_addresses_lower_idx on tracked_email_addresses tea_1 (cost=0.56..8.51 rows=1 width=58) (actual time=0.033..0.034 rows=1 loops=3,156)

  • Index Cond: (lower(email_address) = lower(u_1.email))
  • Filter: (email_address <> ''::text)
37. 3,607.770 3,967.342 ↑ 1.3 3,461 2,892 / 6

Bitmap Heap Scan on tracked_emails te_1 (cost=36.72..107.59 rows=4,458 width=20) (actual time=0.997..8.231 rows=3,461 loops=2,892)

  • Recheck Cond: ((from_env = tea_1.id) OR (from_hdr = tea_1.id))
  • Rows Removed by Index Recheck: 5,560
  • Heap Blocks: exact=673,749 lossy=66,482
38. 0.964 359.572 ↓ 0.0 0 2,892 / 6

BitmapOr (cost=36.72..36.72 rows=4,458 width=0) (actual time=0.746..0.746 rows=0 loops=2,892)

39. 229.914 229.914 ↓ 1.2 3,410 2,892 / 6

Bitmap Index Scan on tracked_emails_from_env_idx (cost=0.00..22.47 rows=2,926 width=0) (actual time=0.477..0.477 rows=3,410 loops=2,892)

  • Index Cond: (from_env = tea_1.id)
40. 128.694 128.694 ↓ 2.2 3,430 2,892 / 6

Bitmap Index Scan on tracked_emails_from_hdr_idx (cost=0.00..12.02 rows=1,532 width=0) (actual time=0.267..0.267 rows=3,430 loops=2,892)

  • Index Cond: (from_hdr = tea_1.id)
41. 13,344.264 13,344.264 ↑ 3.0 3 10,008,198 / 6

Index Only Scan using tracked_emails_x_teas_entity1_id_entity2_id_idx on tracked_emails_x_teas tet_1 (cost=0.56..0.79 rows=9 width=8) (actual time=0.005..0.008 rows=3 loops=10,008,198)

  • Index Cond: (entity1_id = te_1.id)
  • Heap Fetches: 27,593,106
42. 13,796.553 13,796.553 ↑ 1.0 1 27,593,106 / 6

Index Scan using tracked_email_addresses_pkey on tracked_email_addresses ce_1 (cost=0.43..0.45 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=27,593,106)

  • Index Cond: (id = tet_1.entity2_id)
43. 50,587.361 50,587.361 ↓ 0.0 0 27,593,106 / 6

Index Scan using contact_fields_lower_idx1 on contact_fields cf (cost=0.42..0.44 rows=1 width=22) (actual time=0.011..0.011 rows=0 loops=27,593,106)

  • Index Cond: (lower(email1) = lower(ce_1.email_address))
Planning time : 15.362 ms
Execution time : 1,862,534.022 ms