explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pJR5

Settings
# exclusive inclusive rows x rows loops node
1. 7.172 98,532.325 ↑ 1.7 32 1

Merge Left Join (cost=1,989,978.57..2,095,181.64 rows=53 width=132) (actual time=92,946.248..98,532.325 rows=32 loops=1)

  • Merge Cond: ((campaigns.site_id = t.site_id) AND (campaigns.id = ct.campaign_id))
2. 0.139 53,943.135 ↑ 1.7 32 1

Sort (cost=934,865.92..934,866.06 rows=53 width=76) (actual time=53,943.121..53,943.135 rows=32 loops=1)

  • Sort Key: campaigns.site_id, campaigns.id
  • Sort Method: quicksort Memory: 29kB
3. 0.940 53,942.996 ↑ 1.7 32 1

Merge Left Join (cost=934,208.38..934,864.41 rows=53 width=76) (actual time=53,920.036..53,942.996 rows=32 loops=1)

  • Merge Cond: (campaigns.id = campaigns_targets.campaign_id)
4. 0.168 9.843 ↑ 1.7 32 1

Sort (cost=606.23..606.37 rows=53 width=52) (actual time=9.796..9.843 rows=32 loops=1)

  • Sort Key: campaigns.id
  • Sort Method: quicksort Memory: 29kB
5. 9.675 9.675 ↑ 1.7 32 1

Seq Scan on campaigns (cost=0.00..604.71 rows=53 width=52) (actual time=0.077..9.675 rows=32 loops=1)

  • Filter: (running AND (now() <= collection_date))
  • Rows Removed by Filter: 18287
6. 0.000 53,932.213 ↓ 1.9 4,562 1

Finalize GroupAggregate (cost=933,602.14..934,228.13 rows=2,377 width=28) (actual time=53,910.196..53,932.213 rows=4,562 loops=1)

  • Group Key: campaigns_targets.campaign_id
7. 621.485 54,404.644 ↓ 2.2 10,636 1

Gather Merge (cost=933,602.14..934,156.82 rows=4,754 width=28) (actual time=53,910.181..54,404.644 rows=10,636 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 6.359 53,783.159 ↓ 1.6 3,708 3 / 3

Sort (cost=932,602.12..932,608.06 rows=2,377 width=28) (actual time=53,781.319..53,783.159 rows=3,708 loops=3)

  • Sort Key: campaigns_targets.campaign_id
  • Sort Method: quicksort Memory: 393kB
  • Worker 0: Sort Method: quicksort Memory: 391kB
  • Worker 1: Sort Method: quicksort Memory: 393kB
9. 1,404.534 53,776.800 ↓ 1.6 3,789 3 / 3

Partial HashAggregate (cost=932,445.06..932,468.83 rows=2,377 width=28) (actual time=53,770.283..53,776.800 rows=3,789 loops=3)

  • Group Key: campaigns_targets.campaign_id
10. 5,800.354 52,372.266 ↓ 1.1 1,460,414 3 / 3

Parallel Hash Join (cost=205,075.60..894,954.12 rows=1,363,307 width=14) (actual time=49,426.741..52,372.266 rows=1,460,414 loops=3)

  • Hash Cond: (emails.campaigns_target_id = campaigns_targets.id)
11. 42,114.568 42,114.568 ↑ 1.3 1,868,731 3 / 3

Parallel Seq Scan on emails (cost=0.00..653,043.10 rows=2,341,110 width=14) (actual time=3.684..42,114.568 rows=1,868,731 loops=3)

12. 1,411.587 4,457.344 ↑ 1.2 1,605,764 3 / 3

Parallel Hash (cost=172,209.58..172,209.58 rows=2,003,202 width=8) (actual time=4,457.343..4,457.344 rows=1,605,764 loops=3)

  • Buckets: 131072 Batches: 64 Memory Usage: 4032kB
13. 3,045.757 3,045.757 ↑ 1.2 1,605,764 3 / 3

Parallel Seq Scan on campaigns_targets (cost=0.00..172,209.58 rows=2,003,202 width=8) (actual time=0.233..3,045.757 rows=1,605,764 loops=3)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1139673
14. 4,013.879 44,582.018 ↑ 200.7 5,127 1

GroupAggregate (cost=1,055,112.64..1,144,872.70 rows=1,029,241 width=48) (actual time=39,002.139..44,582.018 rows=5,127 loops=1)

  • Group Key: t.site_id, ct.campaign_id
15. 10,987.885 40,568.139 ↓ 1.6 5,187,486 1

Sort (cost=1,055,112.64..1,063,059.41 rows=3,178,706 width=43) (actual time=39,002.097..40,568.139 rows=5,187,486 loops=1)

  • Sort Key: t.site_id, ct.campaign_id
  • Sort Method: external merge Disk: 144016kB
16. 6,837.171 29,580.254 ↓ 1.7 5,308,653 1

Hash Right Join (cost=430,319.79..516,246.26 rows=3,178,706 width=43) (actual time=20,493.829..29,580.254 rows=5,308,653 loops=1)

  • Hash Cond: (v.campaigns_target_id = ct.id)
17. 2,250.619 2,250.619 ↑ 1.0 1,240,989 1

Seq Scan on visits v (cost=0.00..40,200.55 rows=1,241,945 width=19) (actual time=0.351..2,250.619 rows=1,240,989 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 56364
18. 3,202.115 20,492.464 ↓ 1.5 4,817,293 1

Hash (cost=371,959.96..371,959.96 rows=3,178,706 width=24) (actual time=20,492.464..20,492.464 rows=4,817,293 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 4045kB
19. 8,156.808 17,290.349 ↓ 1.5 4,817,293 1

Hash Left Join (cost=87,373.78..371,959.96 rows=3,178,706 width=24) (actual time=2,303.495..17,290.349 rows=4,817,293 loops=1)

  • Hash Cond: (ct.target_id = t.id)
20. 1,808.649 6,850.274 ↓ 1.5 4,817,293 1

Hash Join (cost=664.72..233,656.79 rows=3,178,706 width=20) (actual time=8.893..6,850.274 rows=4,817,293 loops=1)

  • Hash Cond: (ct.campaign_id = c.id)
21. 5,032.929 5,032.929 ↓ 1.0 4,817,293 1

Seq Scan on campaigns_targets ct (cost=0.00..220,368.98 rows=4,807,685 width=20) (actual time=0.099..5,032.929 rows=4,817,293 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 3419018
22. 2.647 8.696 ↑ 1.0 12,133 1

Hash (cost=512.81..512.81 rows=12,153 width=4) (actual time=8.696..8.696 rows=12,133 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 555kB
23. 6.049 6.049 ↑ 1.0 12,133 1

Seq Scan on campaigns c (cost=0.00..512.81 rows=12,153 width=4) (actual time=0.033..6.049 rows=12,133 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 6186
24. 705.383 2,283.267 ↑ 1.0 1,526,457 1

Hash (cost=61,518.92..61,518.92 rows=1,535,371 width=8) (actual time=2,283.267..2,283.267 rows=1,526,457 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2871kB
25. 1,577.884 1,577.884 ↑ 1.0 1,526,457 1

Seq Scan on targets t (cost=0.00..61,518.92 rows=1,535,371 width=8) (actual time=0.024..1,577.884 rows=1,526,457 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1017973