explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8jr7

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 14,265.210 ↑ 1.5 340 1

Limit (cost=4,759,919.57..4,768,251.45 rows=500 width=4,805) (actual time=14,264.942..14,265.210 rows=340 loops=1)

2. 0.209 14,265.188 ↑ 317.1 340 1

Result (cost=4,759,919.57..6,556,471.79 rows=107,812 width=4,805) (actual time=14,264.939..14,265.188 rows=340 loops=1)

3. 1.369 14,259.036 ↑ 317.1 340 1

Sort (cost=4,759,919.57..4,760,189.10 rows=107,812 width=4,804) (actual time=14,258.972..14,259.036 rows=340 loops=1)

  • Sort Key: t4.name, inspections_booking.last_updated_time DESC
  • Sort Method: quicksort Memory: 335kB
4. 1.968 14,257.667 ↑ 317.1 340 1

GroupAggregate (cost=3,852,565.27..4,754,547.42 rows=107,812 width=4,804) (actual time=14,257.143..14,257.667 rows=340 loops=1)

  • Group Key: inspections_booking.id, (CASE WHEN ((inspections_booking.assignment_status)::text = ANY ('{new,assigned,pre-assigned}'::text[])) THEN 'confirmed'::character varying WHEN ((inspections_booking.assignment_executor_id = 252,437) AND ((inspections_booking.assignment_status)::text = 'in-progress'::text) AND inspections_booking.is_approval_needed) THEN 'pending'::character varying WHEN (((inspections_booking.assignment_status)::text = 'in-progress'::text) AND (alternatives: SubPlan 3 or hashed SubPlan 4) AND inspections_booking.is_approval_needed) THEN 'pending'::character varying ELSE COALESCE(inspections_booking.assignment_status, inspections_booking.status) END), t4.name
5. 2.522 14,255.699 ↑ 125.4 860 1

Sort (cost=3,852,565.27..3,852,834.80 rows=107,812 width=4,796) (actual time=14,255.585..14,255.699 rows=860 loops=1)

  • Sort Key: inspections_booking.id, (CASE WHEN ((inspections_booking.assignment_status)::text = ANY ('{new,assigned,pre-assigned}'::text[])) THEN 'confirmed'::character varying WHEN ((inspections_booking.assignment_executor_id = 252,437) AND ((inspections_booking.assignment_status)::text = 'in-progress'::text) AND inspections_booking.is_approval_needed) THEN 'pending'::character varying WHEN (((inspections_booking.assignment_status)::text = 'in-progress'::text) AND (alternatives: SubPlan 3 or hashed SubPlan 4) AND inspections_booking.is_approval_needed) THEN 'pending'::character varying ELSE COALESCE(inspections_booking.assignment_status, inspections_booking.status) END), t4.name
  • Sort Method: quicksort Memory: 864kB
6. 1.657 14,253.177 ↑ 125.4 860 1

Nested Loop (cost=285.25..3,399,144.18 rows=107,812 width=4,796) (actual time=1,447.995..14,253.177 rows=860 loops=1)

7. 79.468 14,237.048 ↑ 125.4 860 1

Merge Left Join (cost=284.96..2,466,097.26 rows=107,812 width=4,737) (actual time=1,439.094..14,237.048 rows=860 loops=1)

  • Merge Cond: (inspections_booking.id = inspections_reportshare.booking_id)
  • Filter: (((hashed SubPlan 5) AND (inspections_booking.factory_information_id = inspections_customorgcontactlocation.id)) OR ((hashed SubPlan 6) AND (inspections_booking.supplier_information_id = inspections_customorgcontactlocation.id)) OR ((hashed SubPlan 7) AND (inspections_booking.retailer_information_id = inspections_customorgcontactlocation.id)) OR (inspections_reportshare.share_to_org_id = ANY ('{317952,304641,312832,312966,294164,252437,252438,252439,252440,316154,308517,293414,308519,300456,308777,308776,293416,308775,316205,300457,293415,300455,312627,316084,312628,308800,308692,308693,313047,309344,305123,301157,311143,314088,309353,309354,311144,308518,339693,316151,316152,316145,313201,316146,308851,316147,312566,316148,312568,316149,316153,312567,316156,316157,313202,316150}'::integer[])))
8. 216.527 12,799.407 ↑ 651.1 340 1

Nested Loop (cost=1.84..2,383,128.14 rows=221,376 width=4,741) (actual time=1,206.934..12,799.407 rows=340 loops=1)

9. 8,271.960 8,271.960 ↑ 1.9 215,546 1

Index Scan using inspections_booking_pkey on inspections_booking (cost=0.42..149,715.32 rows=407,092 width=4,737) (actual time=6.670..8,271.960 rows=215,546 loops=1)

  • Filter: ((book_from_org_id <> 252437) AND (book_to_org_id <> 252437) AND ((executor_id <> 252437) OR (executor_id IS NULL)) AND ((assignment_executor_id <> 252437) OR (assignment_executor_id IS NULL)))
  • Rows Removed by Filter: 199,770
10. 3,233.190 4,310.920 ↓ 0.0 0 215,546

Bitmap Heap Scan on inspections_customorgcontactlocation (cost=1.42..5.48 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=215,546)

  • Recheck Cond: ((inspections_booking.factory_information_id = id) OR (inspections_booking.supplier_information_id = id) OR (inspections_booking.retailer_information_id = id))
  • Filter: (org_id = 252,437)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=276,795
11. 215.546 1,077.730 ↓ 0.0 0 215,546

BitmapOr (cost=1.42..1.42 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=215,546)

12. 431.092 431.092 ↑ 1.0 1 215,546

Bitmap Index Scan on inspections_customorgcontactlocation_pkey (cost=0.00..0.47 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=215,546)

  • Index Cond: (inspections_booking.factory_information_id = id)
13. 215.546 215.546 ↑ 1.0 1 215,546

Bitmap Index Scan on inspections_customorgcontactlocation_pkey (cost=0.00..0.47 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=215,546)

  • Index Cond: (inspections_booking.supplier_information_id = id)
14. 215.546 215.546 ↑ 1.0 1 215,546

Bitmap Index Scan on inspections_customorgcontactlocation_pkey (cost=0.00..0.47 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=215,546)

  • Index Cond: (inspections_booking.retailer_information_id = id)
15. 1,353.724 1,353.724 ↑ 1.1 861,801 1

Index Scan using inspections_reportshare_booking_id_55a9b54f on inspections_reportshare (cost=0.42..33,613.48 rows=913,087 width=8) (actual time=1.362..1,353.724 rows=861,801 loops=1)

16.          

SubPlan (for Merge Left Join)

17. 3.044 3.044 ↑ 1.0 2,332 1

Seq Scan on inspections_bookingtemplatehistory u0_4 (cost=0.00..88.40 rows=2,332 width=4) (actual time=0.417..3.044 rows=2,332 loops=1)

  • Filter: (share_booking_to @> '{factory}'::character varying(20)[])
  • Rows Removed by Filter: 20
18. 0.747 0.747 ↑ 1.0 2,336 1

Seq Scan on inspections_bookingtemplatehistory u0_5 (cost=0.00..88.40 rows=2,336 width=4) (actual time=0.007..0.747 rows=2,336 loops=1)

  • Filter: (share_booking_to @> '{supplier}'::character varying(20)[])
  • Rows Removed by Filter: 16
19. 0.658 0.658 ↑ 1.0 2,330 1

Seq Scan on inspections_bookingtemplatehistory u0_6 (cost=0.00..88.40 rows=2,330 width=4) (actual time=0.006..0.658 rows=2,330 loops=1)

  • Filter: (share_booking_to @> '{retailer}'::character varying(20)[])
  • Rows Removed by Filter: 22
20. 9.460 9.460 ↑ 1.0 1 860

Index Scan using accounts_org_pkey on accounts_org t4 (cost=0.29..0.33 rows=1 width=31) (actual time=0.011..0.011 rows=1 loops=860)

  • Index Cond: (id = inspections_booking.book_to_org_id)
21.          

SubPlan (for Nested Loop)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using inspections_bookingtag_booking_id_7e1e367f on inspections_bookingtag u0_2 (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: (booking_id = inspections_booking.id)
  • Filter: ((value)::text = 'Submitted Pending Approval'::text)
23. 5.012 5.012 ↓ 1.4 85 1

Seq Scan on inspections_bookingtag u0_3 (cost=0.00..562.03 rows=62 width=4) (actual time=2.409..5.012 rows=85 loops=1)

  • Filter: ((value)::text = 'Submitted Pending Approval'::text)
  • Rows Removed by Filter: 29,677
24.          

SubPlan (for Result)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using inspections_bookingtag_booking_id_7e1e367f on inspections_bookingtag u0 (cost=0.29..8.31 rows=1 width=0) (never executed)

  • Index Cond: (booking_id = inspections_booking.id)
  • Filter: ((value)::text = 'Submitted Pending Approval'::text)
26. 5.943 5.943 ↓ 1.4 85 1

Seq Scan on inspections_bookingtag u0_1 (cost=0.00..562.03 rows=62 width=4) (actual time=4.342..5.943 rows=85 loops=1)

  • Filter: ((value)::text = 'Submitted Pending Approval'::text)
  • Rows Removed by Filter: 29,677
Planning time : 2.951 ms
Execution time : 14,265.696 ms