explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bao5 : Optimization for: plan #wJxq

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 397,227.251 ↓ 0.0 0 1

Delete on audition_talent a (cost=19,176,188.58..19,234,542.21 rows=149 width=38) (actual time=397,227.251..397,227.251 rows=0 loops=1)

2.          

CTE sub

3. 748.668 397,227.248 ↓ 0.0 0 1

SetOp Except (cost=19,015,602.03..19,043,131.20 rows=989,134 width=12) (actual time=397,227.248..397,227.248 rows=0 loops=1)

4. 5,972.989 396,478.580 ↓ 2.0 7,340,590 1

Sort (cost=19,015,602.03..19,024,778.42 rows=3,670,556 width=12) (actual time=394,797.458..396,478.580 rows=7,340,590 loops=1)

  • Sort Key: "*SELECT* 1".audition_session_id, "*SELECT* 1".submission_id
  • Sort Method: external merge Disk: 158168kB
5. 673.956 390,505.591 ↓ 2.0 7,340,590 1

Append (cost=0.00..18,552,642.05 rows=3,670,556 width=12) (actual time=7.945..390,505.591 rows=7,340,590 loops=1)

6. 356.677 1,667.814 ↑ 1.0 3,670,295 1

Subquery Scan on *SELECT* 1 (cost=0.00..96,783.90 rows=3,670,295 width=12) (actual time=7.944..1,667.814 rows=3,670,295 loops=1)

7. 1,311.137 1,311.137 ↑ 1.0 3,670,295 1

Seq Scan on audition_talent a_1 (cost=0.00..60,080.95 rows=3,670,295 width=8) (actual time=7.943..1,311.137 rows=3,670,295 loops=1)

8. 882.998 388,163.821 ↓ 14,062.4 3,670,295 1

Subquery Scan on *SELECT* 2 (cost=2,139,194.29..18,455,858.15 rows=261 width=12) (actual time=123,601.002..388,163.821 rows=3,670,295 loops=1)

9. 2,754.585 387,280.823 ↓ 14,062.4 3,670,295 1

Nested Loop (cost=2,139,194.29..18,455,855.54 rows=261 width=8) (actual time=123,601.002..387,280.823 rows=3,670,295 loops=1)

10. 12,864.364 369,845.058 ↓ 14,564.7 3,670,295 1

Hash Join (cost=2,139,193.87..18,454,841.38 rows=252 width=11) (actual time=123,598.461..369,845.058 rows=3,670,295 loops=1)

  • Hash Cond: ((r.legacy_role_id = temp.legacy_role_id) AND (s.profile_id = p.profile_id))
11. 86,569.573 308,740.834 ↑ 3.6 25,972,087 1

Hash Join (cost=134,701.01..13,171,849.27 rows=93,217,429 width=15) (actual time=75,347.809..308,740.834 rows=25,972,087 loops=1)

  • Hash Cond: (s.role_id = r.role_id)
12. 218,477.335 218,477.335 ↑ 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.011..218,477.335 rows=348,094,921 loops=1)

  • Filter: (submission_id IS NOT NULL)
13. 77.576 3,693.926 ↑ 1.5 311,110 1

Hash (cost=126,461.98..126,461.98 rows=473,922 width=11) (actual time=3,693.926..3,693.926 rows=311,110 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 10776kB
14. 3,616.350 3,616.350 ↑ 1.5 311,110 1

Seq Scan on role r (cost=0.00..126,461.98 rows=473,922 width=11) (actual time=2,037.072..3,616.350 rows=311,110 loops=1)

  • Filter: (((legacy_instance)::bpchar = 'NY'::bpchar) AND ((legacy_source_table)::text = 'P3'::text))
  • Rows Removed by Filter: 1460511
15. 1,756.545 48,239.860 ↓ 1.1 8,608,859 1

Hash (cost=1,850,845.76..1,850,845.76 rows=7,727,607 width=15) (actual time=48,239.860..48,239.860 rows=8,608,859 loops=1)

  • Buckets: 524288 Batches: 32 Memory Usage: 16712kB
16. 2,871.740 46,483.315 ↓ 1.1 8,608,859 1

Merge Join (cost=1,700,193.74..1,850,845.76 rows=7,727,607 width=15) (actual time=34,143.234..46,483.315 rows=8,608,859 loops=1)

  • Merge Cond: ((p.legacy_profile_id)::text = (temp.legacy_profile_id)::text)
17. 8,653.493 12,481.770 ↓ 1.0 1,881,312 1

Sort (cost=369,454.71..374,138.04 rows=1,873,330 width=17) (actual time=10,390.428..12,481.770 rows=1,881,312 loops=1)

  • Sort Key: p.legacy_profile_id
  • Sort Method: external merge Disk: 47840kB
18. 3,828.277 3,828.277 ↓ 1.0 1,881,312 1

Seq Scan on profile p (cost=0.00..135,860.70 rows=1,873,330 width=17) (actual time=8.196..3,828.277 rows=1,881,312 loops=1)

  • Filter: ((legacy_instance)::bpchar = 'NY'::bpchar)
  • Rows Removed by Filter: 1912019
19. 958.160 31,129.805 ↑ 1.0 8,608,859 1

Materialize (cost=1,330,646.00..1,373,691.00 rows=8,609,000 width=18) (actual time=23,752.799..31,129.805 rows=8,608,859 loops=1)

20. 26,983.765 30,171.645 ↑ 1.0 8,608,859 1

Sort (cost=1,330,646.00..1,352,168.50 rows=8,609,000 width=18) (actual time=23,752.794..30,171.645 rows=8,608,859 loops=1)

  • Sort Key: temp.legacy_profile_id
  • Sort Method: external merge Disk: 252608kB
21. 3,187.880 3,187.880 ↑ 1.0 8,608,859 1

Seq Scan on temp_audition_talent temp (cost=0.00..162,446.50 rows=8,609,000 width=18) (actual time=5.956..3,187.880 rows=8,608,859 loops=1)

  • Filter: (legacy_instance = 'NY'::bpchar)
22. 14,681.180 14,681.180 ↑ 1.0 1 3,670,295

Index Scan using ix_uq_audition_session_legacy on audition_session aus (cost=0.42..4.02 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=3,670,295)

  • Index Cond: ((legacy_session_id = temp.legacy_audition_session_id) AND (legacy_instance = 'NY'::bpchar))
  • Filter: (audition_session_id IS NOT NULL)
23. 0.000 397,227.249 ↓ 0.0 0 1

Hash Join (cost=133,057.38..191,411.01 rows=149 width=38) (actual time=397,227.249..397,227.249 rows=0 loops=1)

  • Hash Cond: ((sub.audition_session_id = a.audition_session_id) AND (sub.submission_id = a.submission_id))
24. 397,227.249 397,227.249 ↓ 0.0 0 1

CTE Scan on sub (cost=0.00..19,782.68 rows=989,134 width=40) (actual time=397,227.248..397,227.249 rows=0 loops=1)

25. 0.000 0.000 ↓ 0.0 0

Hash (cost=60,080.95..60,080.95 rows=3,670,295 width=14) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on audition_talent a (cost=0.00..60,080.95 rows=3,670,295 width=14) (never executed)

Planning time : 44.698 ms
Execution time : 397,294.342 ms