explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MzJt

Settings
# exclusive inclusive rows x rows loops node
1. 1,554.551 20,892.860 ↑ 61.5 145,972 1

Merge Join (cost=4,035,862.35..4,125,584.99 rows=8,971,964 width=12) (actual time=15,628.685..20,892.860 rows=145,972 loops=1)

  • Merge Cond: (deployment_merge_requests.ctid = t.ctid)
2. 7,790.367 12,311.278 ↑ 1.0 17,499,337 1

Sort (cost=3,377,250.51..3,422,110.33 rows=17,943,928 width=18) (actual time=8,613.631..12,311.278 rows=17,499,337 loops=1)

  • Sort Key: deployment_merge_requests.ctid
  • Sort Method: external merge Disk: 426272kB
3. 4,520.911 4,520.911 ↓ 1.0 18,018,132 1

Seq Scan on deployment_merge_requests (cost=0.00..505,575.28 rows=17,943,928 width=18) (actual time=0.051..4,520.911 rows=18,018,132 loops=1)

4. 34.462 7,027.031 ↓ 729.9 145,972 1

Sort (cost=658,611.84..658,612.34 rows=200 width=6) (actual time=7,014.939..7,027.031 rows=145,972 loops=1)

  • Sort Key: t.ctid
  • Sort Method: quicksort Memory: 12987kB
5. 21.029 6,992.569 ↓ 729.9 145,972 1

Unique (cost=658,125.81..658,604.19 rows=200 width=6) (actual time=6,952.263..6,992.569 rows=145,972 loops=1)

6. 91.744 6,971.540 ↓ 1.5 145,972 1

Sort (cost=658,125.81..658,365.00 rows=95,676 width=6) (actual time=6,952.262..6,971.540 rows=145,972 loops=1)

  • Sort Key: t.ctid
  • Sort Method: quicksort Memory: 12987kB
7. 22.643 6,879.796 ↓ 1.5 145,972 1

Subquery Scan on t (cost=640,164.65..650,210.60 rows=95,676 width=6) (actual time=6,764.461..6,879.796 rows=145,972 loops=1)

  • Filter: (t.rnum > 1)
  • Rows Removed by Filter: 11514
8. 57.394 6,857.153 ↑ 1.8 157,486 1

WindowAgg (cost=640,164.65..646,622.76 rows=287,027 width=26) (actual time=6,764.452..6,857.153 rows=157,486 loops=1)

9. 157.299 6,799.759 ↑ 1.8 157,486 1

Sort (cost=640,164.65..640,882.22 rows=287,027 width=18) (actual time=6,764.432..6,799.759 rows=157,486 loops=1)

  • Sort Key: mrd.merge_request_id, deployments.environment_id, deployments.id
  • Sort Method: quicksort Memory: 18448kB
10. 1,796.349 6,642.460 ↑ 1.8 157,486 1

Hash Join (cost=38,409.19..614,144.47 rows=287,027 width=18) (actual time=2,431.048..6,642.460 rows=157,486 loops=1)

  • Hash Cond: (mrd.deployment_id = deployments.id)
11. 2,419.847 2,419.847 ↓ 1.0 18,018,132 1

Seq Scan on deployment_merge_requests mrd (cost=0.00..505,575.28 rows=17,943,928 width=14) (actual time=0.016..2,419.847 rows=18,018,132 loops=1)

12. 275.818 2,426.264 ↓ 1.0 897,347 1

Hash (cost=27,336.30..27,336.30 rows=885,831 width=8) (actual time=2,426.263..2,426.264 rows=897,347 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 43245kB
13. 2,150.446 2,150.446 ↓ 1.0 897,347 1

Index Only Scan using index_deployments_on_environment_id_and_id on deployments (cost=0.56..27,336.30 rows=885,831 width=8) (actual time=1.203..2,150.446 rows=897,347 loops=1)

  • Index Cond: ((environment_id >= 10000) AND (environment_id <= 20000))
  • Heap Fetches: 16175
Planning time : 4.614 ms
Execution time : 20,998.061 ms