explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MJUl

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 86,755.775 ↓ 100.0 100 1

Limit (cost=127,712.36..127,712.37 rows=1 width=699) (actual time=86,755.741..86,755.775 rows=100 loops=1)

2. 8.669 86,755.760 ↓ 100.0 100 1

Sort (cost=127,712.36..127,712.37 rows=1 width=699) (actual time=86,755.741..86,755.760 rows=100 loops=1)

  • Sort Key: i.datalake_updated_at
  • Sort Method: top-N heapsort Memory: 127kB
3. 62.387 86,747.091 ↓ 2,793.0 2,793 1

Nested Loop Left Join (cost=82,166.37..127,712.35 rows=1 width=699) (actual time=960.942..86,747.091 rows=2,793 loops=1)

4. 7.302 86,676.325 ↓ 2,793.0 2,793 1

Nested Loop Left Join (cost=82,166.08..127,703.92 rows=1 width=481) (actual time=960.890..86,676.325 rows=2,793 loops=1)

5. 7.692 86,660.644 ↓ 2,793.0 2,793 1

Nested Loop Left Join (cost=82,165.79..127,695.60 rows=1 width=478) (actual time=960.883..86,660.644 rows=2,793 loops=1)

6. 8.653 86,641.780 ↓ 2,793.0 2,793 1

Nested Loop Left Join (cost=82,165.50..127,687.28 rows=1 width=475) (actual time=960.877..86,641.780 rows=2,793 loops=1)

7. 15.682 86,616.369 ↓ 2,793.0 2,793 1

Merge Right Join (cost=82,165.21..127,678.96 rows=1 width=472) (actual time=960.856..86,616.369 rows=2,793 loops=1)

  • Merge Cond: (s.booking_id = i.id)
8. 81.303 271.871 ↓ 1.1 95,031 1

GroupAggregate (cost=38,505.52..42,173.64 rows=86,279 width=36) (actual time=132.587..271.871 rows=95,031 loops=1)

  • Group Key: s.booking_id
9. 151.183 190.568 ↓ 1.0 349,022 1

Sort (cost=38,505.52..39,368.73 rows=345,284 width=8) (actual time=132.566..190.568 rows=349,022 loops=1)

  • Sort Key: s.booking_id
  • Sort Method: quicksort Memory: 28690kB
10. 39.385 39.385 ↓ 1.0 349,890 1

Seq Scan on inspections_reportshare s (cost=0.00..6,743.84 rows=345,284 width=8) (actual time=0.007..39.385 rows=349,890 loops=1)

11. 10.269 86,328.816 ↓ 2,793.0 2,793 1

Materialize (cost=43,659.69..84,426.83 rows=1 width=440) (actual time=827.914..86,328.816 rows=2,793 loops=1)

12. 15,168.006 86,318.547 ↓ 2,793.0 2,793 1

Nested Loop Left Join (cost=43,659.69..84,426.82 rows=1 width=440) (actual time=827.907..86,318.547 rows=2,793 loops=1)

  • Join Filter: (d.booking_id = i.id)
  • Rows Removed by Join Filter: 247325740
13. 7.938 102.207 ↓ 2,793.0 2,793 1

Nested Loop (cost=0.70..38,173.75 rows=1 width=400) (actual time=0.167..102.207 rows=2,793 loops=1)

14. 30.030 41.706 ↑ 1.0 7,509 1

GroupAggregate (cost=0.28..855.29 rows=7,509 width=36) (actual time=0.062..41.706 rows=7,509 loops=1)

  • Group Key: bt.booking_id
  • Filter: (array_length(array_agg(upper(bt.value)), 1) > 0)
15. 11.676 11.676 ↑ 1.0 7,515 1

Index Scan using idx_raw_sight_21_inspections_bookingtag_booking_id on inspections_bookingtag bt (cost=0.28..648.72 rows=7,515 width=21) (actual time=0.038..11.676 rows=7,515 loops=1)

16. 52.563 52.563 ↓ 0.0 0 7,509

Index Scan using booking_pkey on booking i (cost=0.42..4.95 rows=1 width=368) (actual time=0.006..0.007 rows=0 loops=7,509)

  • Index Cond: (id = bt.booking_id)
  • Filter: ((book_to_org_id IS NOT NULL) AND (inspection_uuid IS NOT NULL) AND (upper((inspection_result)::text) = ANY ('{PASS,FAIL}'::text[])) AND (upper((inspection_status)::text) = 'COMPLETED'::text))
  • Rows Removed by Filter: 1
17. 71,003.568 71,048.334 ↓ 1.1 88,553 2,793

HashAggregate (cost=43,658.99..44,457.17 rows=79,818 width=52) (actual time=0.285..25.438 rows=88,553 loops=2,793)

  • Group Key: d.booking_id
18. 44.766 44.766 ↓ 1.0 225,414 1

Seq Scan on inspections_inspectionfixedgroup d (cost=0.00..30,076.24 rows=217,324 width=60) (actual time=0.004..44.766 rows=225,414 loops=1)

19. 16.758 16.758 ↑ 1.0 1 2,793

Index Scan using sm_organization_pkey on sm_organization org_o (cost=0.29..8.31 rows=1 width=7) (actual time=0.006..0.006 rows=1 loops=2,793)

  • Index Cond: (i.executor_id = id)
20. 11.172 11.172 ↑ 1.0 1 2,793

Index Scan using sm_organization_pkey on sm_organization org_e (cost=0.29..8.31 rows=1 width=7) (actual time=0.003..0.004 rows=1 loops=2,793)

  • Index Cond: (i.assignment_executor_id = id)
21. 8.379 8.379 ↑ 1.0 1 2,793

Index Scan using sm_organization_pkey on sm_organization org_f (cost=0.29..8.31 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=2,793)

  • Index Cond: (i.factory_id = id)
22. 8.379 8.379 ↑ 1.0 1 2,793

Index Scan using sm_organization_pkey on sm_organization org_s (cost=0.29..8.31 rows=1 width=7) (actual time=0.002..0.003 rows=1 loops=2,793)

  • Index Cond: (i.supplier_id = id)
Planning time : 3.372 ms
Execution time : 86,758.843 ms