explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EClD

Settings
# exclusive inclusive rows x rows loops node
1. 170.865 2,638.237 ↓ 8,248.5 90,733 1

Sort (cost=164,969.94..164,969.97 rows=11 width=699) (actual time=2,615.243..2,638.237 rows=90,733 loops=1)

  • Sort Key: i.datalake_updated_at
  • Sort Method: quicksort Memory: 80077kB
2. 287.931 2,467.372 ↓ 8,248.5 90,733 1

Hash Left Join (cost=92,101.26..164,969.75 rows=11 width=699) (actual time=1,155.799..2,467.372 rows=90,733 loops=1)

  • Hash Cond: (i.id = btx.booking_id)
3. 57.910 2,160.279 ↓ 8,248.5 90,733 1

Hash Left Join (cost=91,426.46..164,283.41 rows=11 width=452) (actual time=1,136.479..2,160.279 rows=90,733 loops=1)

  • Hash Cond: (i.id = dd.booking_id)
4. 74.675 1,248.908 ↓ 8,248.5 90,733 1

Nested Loop Left Join (cost=45,173.39..117,920.51 rows=11 width=412) (actual time=282.516..1,248.908 rows=90,733 loops=1)

5. 77.545 1,083.500 ↓ 8,248.5 90,733 1

Nested Loop Left Join (cost=45,173.10..117,829.02 rows=11 width=409) (actual time=282.511..1,083.500 rows=90,733 loops=1)

6. 81.022 915.222 ↓ 8,248.5 90,733 1

Nested Loop Left Join (cost=45,172.81..117,737.53 rows=11 width=406) (actual time=282.503..915.222 rows=90,733 loops=1)

7. 47.298 743.467 ↓ 8,248.5 90,733 1

Nested Loop Left Join (cost=45,172.52..117,646.03 rows=11 width=403) (actual time=282.489..743.467 rows=90,733 loops=1)

8. 53.603 605.436 ↓ 8,248.5 90,733 1

Hash Left Join (cost=45,172.23..117,554.54 rows=11 width=400) (actual time=282.483..605.436 rows=90,733 loops=1)

  • Hash Cond: (i.id = report_shares.booking_id)
9. 270.002 270.002 ↓ 8,248.5 90,733 1

Seq Scan on booking i (cost=0.00..72,260.22 rows=11 width=368) (actual time=0.099..270.002 rows=90,733 loops=1)

  • 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: 119918
10. 18.949 281.831 ↓ 1.1 96,456 1

Hash (cost=44,063.03..44,063.03 rows=88,736 width=36) (actual time=281.831..281.831 rows=96,456 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8060kB
11. 8.750 262.882 ↓ 1.1 96,456 1

Subquery Scan on report_shares (cost=39,414.46..44,063.03 rows=88,736 width=36) (actual time=146.044..262.882 rows=96,456 loops=1)

12. 70.665 254.132 ↓ 1.1 96,456 1

GroupAggregate (cost=39,414.46..43,175.67 rows=88,736 width=36) (actual time=146.043..254.132 rows=96,456 loops=1)

  • Group Key: s.booking_id
13. 138.387 183.467 ↓ 1.0 354,229 1

Sort (cost=39,414.46..40,298.46 rows=353,601 width=8) (actual time=146.028..183.467 rows=354,229 loops=1)

  • Sort Key: s.booking_id
  • Sort Method: quicksort Memory: 28893kB
14. 45.080 45.080 ↓ 1.0 354,229 1

Seq Scan on inspections_reportshare s (cost=0.00..6,827.01 rows=353,601 width=8) (actual time=0.004..45.080 rows=354,229 loops=1)

15. 90.733 90.733 ↑ 1.0 1 90,733

Index Scan using sm_organization_pkey on sm_organization org_o (cost=0.29..8.31 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=90,733)

  • Index Cond: (i.executor_id = id)
16. 90.733 90.733 ↑ 1.0 1 90,733

Index Scan using sm_organization_pkey on sm_organization org_e (cost=0.29..8.31 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=90,733)

  • Index Cond: (i.assignment_executor_id = id)
17. 90.733 90.733 ↑ 1.0 1 90,733

Index Scan using sm_organization_pkey on sm_organization org_f (cost=0.29..8.31 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=90,733)

  • Index Cond: (i.factory_id = id)
18. 90.733 90.733 ↑ 1.0 1 90,733

Index Scan using sm_organization_pkey on sm_organization org_s (cost=0.29..8.31 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=90,733)

  • Index Cond: (i.supplier_id = id)
19. 17.437 853.461 ↓ 1.1 89,337 1

Hash (cost=45,255.35..45,255.35 rows=79,818 width=44) (actual time=853.461..853.461 rows=89,337 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7655kB
20. 8.753 836.024 ↓ 1.1 89,337 1

Subquery Scan on dd (cost=43,658.99..45,255.35 rows=79,818 width=44) (actual time=797.553..836.024 rows=89,337 loops=1)

21. 780.544 827.271 ↓ 1.1 89,337 1

HashAggregate (cost=43,658.99..44,457.17 rows=79,818 width=52) (actual time=797.551..827.271 rows=89,337 loops=1)

  • Group Key: d.booking_id
22. 46.727 46.727 ↓ 1.0 227,211 1

Seq Scan on inspections_inspectionfixedgroup d (cost=0.00..30,076.24 rows=217,324 width=60) (actual time=0.005..46.727 rows=227,211 loops=1)

23. 1.257 19.162 ↓ 1.0 7,620 1

Hash (cost=580.93..580.93 rows=7,509 width=37) (actual time=19.162..19.162 rows=7,620 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 685kB
24. 0.721 17.905 ↓ 1.0 7,620 1

Subquery Scan on btx (cost=355.66..580.93 rows=7,509 width=37) (actual time=13.571..17.905 rows=7,620 loops=1)

25. 16.424 17.184 ↓ 1.0 7,620 1

HashAggregate (cost=355.66..505.84 rows=7,509 width=37) (actual time=13.570..17.184 rows=7,620 loops=1)

  • Group Key: bt.booking_id
26. 0.760 0.760 ↓ 1.0 7,627 1

Seq Scan on inspections_bookingtag bt (cost=0.00..224.15 rows=7,515 width=21) (actual time=0.009..0.760 rows=7,627 loops=1)

Planning time : 3.766 ms
Execution time : 2,677.125 ms