explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gUyA

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 3,614.812 ↑ 1.0 30 1

Limit (cost=111,221.60..111,221.67 rows=30 width=240) (actual time=3,614.804..3,614.812 rows=30 loops=1)

2. 34.108 3,614.800 ↑ 358.7 150 1

Sort (cost=111,221.30..111,355.80 rows=53,800 width=240) (actual time=3,614.796..3,614.800 rows=150 loops=1)

  • Sort Key: mission_inventory_aggregated.date
  • Sort Method: top-N heapsort Memory: 807kB
3. 7.326 3,580.692 ↑ 2.0 26,736 1

Subquery Scan on mission_inventory_aggregated (cost=104,838.24..109,007.74 rows=53,800 width=240) (actual time=1,792.125..3,580.692 rows=26,736 loops=1)

4. 1,513.385 3,573.366 ↑ 2.0 26,736 1

GroupAggregate (cost=104,838.24..108,469.74 rows=53,800 width=240) (actual time=1,792.124..3,573.366 rows=26,736 loops=1)

  • Group Key: mb."scannedOn", b.id, i.name, i.code, i.description, i.notes, i.condition, i."daysLeft", m.id, i."recordType", i."verifiedRecord", i."verificationDate", i."verificationSource", i.correctness
5. 1,900.671 2,059.981 ↑ 1.0 53,766 1

Sort (cost=104,838.24..104,972.74 rows=53,800 width=1,798) (actual time=1,791.933..2,059.981 rows=53,766 loops=1)

  • Sort Key: mb."scannedOn", b.id, i.name, i.code, i.description, i.notes, i.condition, i."daysLeft", m.id, i."recordType", i."verifiedRecord", i."verificationDate", i."verificationSource", i.correctness
  • Sort Method: external merge Disk: 87,864kB
6. 18.045 159.310 ↑ 1.0 53,766 1

Hash Join (cost=2,124.72..16,757.82 rows=53,800 width=1,798) (actual time=20.418..159.310 rows=53,766 loops=1)

  • Hash Cond: (i."inventoryGalleryId" = g.id)
7. 17.150 137.030 ↑ 1.0 53,766 1

Hash Join (cost=1,582.33..16,073.95 rows=53,800 width=510) (actual time=16.147..137.030 rows=53,766 loops=1)

  • Hash Cond: (mb."binId" = b.id)
8. 15.111 119.637 ↑ 1.0 53,766 1

Hash Join (cost=1,553.78..15,903.41 rows=53,800 width=492) (actual time=15.895..119.637 rows=53,766 loops=1)

  • Hash Cond: (mb."missionId" = m.id)
9. 15.288 104.466 ↑ 1.0 53,766 1

Hash Join (cost=1,532.61..15,729.27 rows=53,800 width=443) (actual time=15.826..104.466 rows=53,766 loops=1)

  • Hash Cond: (i."missionBinId" = mb.id)
10. 32.003 73.673 ↑ 1.0 53,766 1

Merge Left Join (cost=0.58..14,056.01 rows=53,800 width=431) (actual time=0.030..73.673 rows=53,766 loops=1)

  • Merge Cond: (i.id = d."inventoryItemId")
11. 29.898 29.898 ↑ 1.0 53,766 1

Index Scan using "PK_94f5cbcb5f280f2f30bd4a9fd90" on inventory_item i (cost=0.29..10,231.34 rows=53,800 width=99) (actual time=0.014..29.898 rows=53,766 loops=1)

  • Filter: ("deletedAt" IS NULL)
12. 11.772 11.772 ↑ 1.0 40,277 1

Index Scan using "IDX_e7e8c59992a43a4733c0295e29" on detection d (cost=0.29..3,320.71 rows=40,279 width=340) (actual time=0.013..11.772 rows=40,277 loops=1)

13. 5.823 15.505 ↓ 1.0 40,560 1

Hash (cost=1,026.01..1,026.01 rows=40,481 width=20) (actual time=15.505..15.505 rows=40,560 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,572kB
14. 9.682 9.682 ↓ 1.0 40,560 1

Seq Scan on mission_bin mb (cost=0.00..1,026.01 rows=40,481 width=20) (actual time=0.011..9.682 rows=40,560 loops=1)

  • Filter: ("rowStatus" <> 3)
15. 0.017 0.060 ↓ 1.1 52 1

Hash (cost=20.59..20.59 rows=47 width=53) (actual time=0.060..0.060 rows=52 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
16. 0.043 0.043 ↓ 1.1 52 1

Seq Scan on mission m (cost=0.00..20.59 rows=47 width=53) (actual time=0.011..0.043 rows=52 loops=1)

  • Filter: ((NOT deleted) AND (date > '2020-09-16 00:00:00+00'::timestamp with time zone))
17. 0.121 0.243 ↑ 1.0 780 1

Hash (cost=18.80..18.80 rows=780 width=22) (actual time=0.242..0.243 rows=780 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
18. 0.122 0.122 ↑ 1.0 780 1

Seq Scan on bin b (cost=0.00..18.80 rows=780 width=22) (actual time=0.014..0.122 rows=780 loops=1)

19. 2.531 4.235 ↑ 1.0 2,373 1

Hash (cost=512.73..512.73 rows=2,373 width=1,288) (actual time=4.235..4.235 rows=2,373 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 3,099kB
20. 1.704 1.704 ↑ 1.0 2,373 1

Seq Scan on inventory_gallery g (cost=0.00..512.73 rows=2,373 width=1,288) (actual time=0.017..1.704 rows=2,373 loops=1)

Planning time : 500.582 ms
Execution time : 4,713.853 ms