explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PAr

Settings
# exclusive inclusive rows x rows loops node
1. 299,033.817 892,763.941 ↓ 0.0 0 1

Insert on audition_talent (cost=2,576,080.75..20,683,092.99 rows=403 width=29) (actual time=892,763.941..892,763.941 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: ix_uq_audition_session_submission_id
  • Conflict Filter: (audition_talent.is_checkin IS DISTINCT FROM excluded.is_checkin)
  • Tuples Inserted: 10312607
  • Conflicting Tuples: 0
2. 119,320.992 593,730.124 ↓ 25,589.6 10,312,607 1

Hash Join (cost=2,576,080.75..20,683,092.99 rows=403 width=29) (actual time=75,236.527..593,730.124 rows=10,312,607 loops=1)

  • Hash Cond: ((r.legacy_role_id = temp.legacy_role_id) AND (s.profile_id = p.profile_id))
3. 141,381.504 403,364.224 ↓ 1.7 257,795,919 1

Hash Join (cost=140,245.69..13,178,950.96 rows=155,965,641 width=15) (actual time=4,170.576..403,364.224 rows=257,795,919 loops=1)

  • Hash Cond: (s.role_id = r.role_id)
4. 257,814.193 257,814.193 ↑ 1.0 348,094,921 1

Seq Scan on submission s (cost=0.00..8,721,715.76 rows=348,094,976 width=12) (actual time=0.005..257,814.193 rows=348,094,921 loops=1)

5. 210.358 4,168.527 ↓ 1.2 919,140 1

Hash (cost=126,461.98..126,461.98 rows=792,937 width=11) (actual time=4,168.527..4,168.527 rows=919,140 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 13994kB
6. 3,958.169 3,958.169 ↓ 1.2 919,140 1

Seq Scan on role r (cost=0.00..126,461.98 rows=792,937 width=11) (actual time=155.203..3,958.169 rows=919,140 loops=1)

  • Filter: (((legacy_instance)::bpchar = 'LA'::bpchar) AND ((legacy_source_table)::text = 'P3'::text))
  • Rows Removed by Filter: 852481
7. 2,642.733 71,044.908 ↓ 1.7 12,744,578 1

Hash (cost=2,289,361.17..2,289,361.17 rows=7,366,859 width=16) (actual time=71,044.908..71,044.908 rows=12,744,578 loops=1)

  • Buckets: 524288 (originally 524288) Batches: 64 (originally 32) Memory Usage: 18526kB
8. 3,396.098 68,402.175 ↓ 1.7 12,744,578 1

Merge Join (cost=2,125,538.02..2,289,361.17 rows=7,366,859 width=16) (actual time=53,599.018..68,402.175 rows=12,744,578 loops=1)

  • Merge Cond: ((p.legacy_profile_id)::text = (temp.legacy_profile_id)::text)
9. 6,486.828 10,948.575 ↓ 1.0 1,385,120 1

Sort (cost=304,644.75..308,089.89 rows=1,378,054 width=17) (actual time=9,722.676..10,948.575 rows=1,385,120 loops=1)

  • Sort Key: p.legacy_profile_id
  • Sort Method: external merge Disk: 35264kB
10. 4,461.747 4,461.747 ↓ 1.0 1,385,120 1

Seq Scan on profile p (cost=0.00..135,860.70 rows=1,378,054 width=17) (actual time=6.690..4,461.747 rows=1,385,120 loops=1)

  • Filter: ((legacy_instance)::bpchar = 'LA'::bpchar)
  • Rows Removed by Filter: 2315692
11. 1,378.124 54,057.502 ↓ 1.1 12,744,578 1

Materialize (cost=1,820,824.81..1,876,608.10 rows=11,156,658 width=19) (actual time=43,876.334..54,057.502 rows=12,744,578 loops=1)

12. 45,451.348 52,679.378 ↓ 1.1 12,744,578 1

Sort (cost=1,820,824.81..1,848,716.46 rows=11,156,658 width=19) (actual time=43,876.328..52,679.378 rows=12,744,578 loops=1)

  • Sort Key: temp.legacy_profile_id
  • Sort Method: external merge Disk: 374144kB
13. 4,655.461 7,228.030 ↓ 1.1 12,744,578 1

Hash Join (cost=12,119.36..286,057.81 rows=11,156,658 width=19) (actual time=495.037..7,228.030 rows=12,744,578 loops=1)

  • Hash Cond: (temp.legacy_audition_session_id = aus.legacy_session_id)
14. 2,078.639 2,078.639 ↑ 1.0 12,744,578 1

Seq Scan on temp_audition_talent temp (cost=0.00..240,483.23 rows=12,744,578 width=19) (actual time=0.133..2,078.639 rows=12,744,578 loops=1)

  • Filter: (legacy_instance = 'LA'::bpchar)
15. 40.795 493.930 ↓ 1.0 177,276 1

Hash (cost=9,907.70..9,907.70 rows=176,933 width=11) (actual time=493.930..493.930 rows=177,276 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9666kB
16. 453.135 453.135 ↓ 1.0 177,276 1

Seq Scan on audition_session aus (cost=0.00..9,907.70 rows=176,933 width=11) (actual time=10.070..453.135 rows=177,276 loops=1)

  • Filter: (legacy_instance = 'LA'::bpchar)
  • Rows Removed by Filter: 191180
Planning time : 47.514 ms
Execution time : 10,421,599.348 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint fk_audition_talent_submission_1 9,322,121.507 ms 10312607 0.904 ms
for constraint fk_audition_talent_audition_session_1 203,579.594 ms 10312607 0.020 ms