explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1eUZ

Settings
# exclusive inclusive rows x rows loops node
1. 11.112 111,293.943 ↓ 2,194.3 6,583 1

Sort (cost=13,617.61..13,617.62 rows=3 width=451) (actual time=111,291.026..111,293.943 rows=6,583 loops=1)

  • Output: oc.uuid, ((oc.created_date - '03:00:00'::interval hour)), ((oc.last_updated_date - '03:00:00'::interval hour)), (to_char((smt_2.created_date - oc.created_date), 'hh24:mi:ss'::text)), r.name, cl.respon
  • Sort Key: ((oc.created_date - '03:00:00'::interval hour)) DESC
  • Sort Method: quicksort Memory: 3,587kB
  • Buffers: shared hit=1,229,493 read=749,063 dirtied=504
2. 12.204 111,282.831 ↓ 2,194.3 6,583 1

Group (cost=13,617.39..13,617.59 rows=3 width=451) (actual time=111,267.585..111,282.831 rows=6,583 loops=1)

  • Output: oc.uuid, (oc.created_date - '03:00:00'::interval hour), (oc.last_updated_date - '03:00:00'::interval hour), (to_char((smt_2.created_date - oc.created_date), 'hh24:mi:ss'::text)), r.name, cl.resp
  • Group Key: us.name, us.email, oc.uuid, r.name, cl.responsible, cl.reason, (to_char((smt.created_date - oc.created_date), 'hh24:mi:ss'::text)), (to_char((smt_1.created_date - smt.created_date), 'hh24:mi:
  • Buffers: shared hit=1,229,490 read=749,063 dirtied=504
3. 41.447 111,270.627 ↓ 2,194.3 6,583 1

Sort (cost=13,617.39..13,617.39 rows=3 width=734) (actual time=111,267.569..111,270.627 rows=6,583 loops=1)

  • Output: oc.uuid, (to_char((smt_2.created_date - oc.created_date), 'hh24:mi:ss'::text)), r.name, cl.responsible, us.name, us.email, cl.reason, gr.name, gr.type, (to_char((smt.created_date - oc.crea
  • Sort Key: us.name, us.email, oc.uuid, r.name, cl.responsible, cl.reason, (to_char((smt.created_date - oc.created_date), 'hh24:mi:ss'::text)), (to_char((smt_1.created_date - smt.created_date), 'hh2
  • Sort Method: quicksort Memory: 6,891kB
  • Buffers: shared hit=1,229,490 read=749,063 dirtied=504
4. 66.359 111,229.180 ↓ 2,194.3 6,583 1

Nested Loop Left Join (cost=33.55..13,617.36 rows=3 width=734) (actual time=9.675..111,229.180 rows=6,583 loops=1)

  • Output: oc.uuid, to_char((smt_2.created_date - oc.created_date), 'hh24:mi:ss'::text), r.name, cl.responsible, us.name, us.email, cl.reason, gr.name, gr.type, to_char((smt.created_date - oc.c
  • Buffers: shared hit=1,229,487 read=749,063 dirtied=504
5. 22.333 110,267.533 ↓ 2,194.3 6,583 1

Nested Loop Left Join (cost=32.27..13,357.19 rows=3 width=638) (actual time=9.398..110,267.533 rows=6,583 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, us.name, us.email, r.name, cl.responsible, cl.reason, g
  • Buffers: shared hit=889,655 read=748,052 dirtied=504
6. 21.072 109,705.394 ↓ 2,194.3 6,583 1

Nested Loop Left Join (cost=30.99..13,097.08 rows=3 width=630) (actual time=9.255..109,705.394 rows=6,583 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, us.name, us.email, r.name, cl.responsible, cl.rea
  • Buffers: shared hit=548,813 read=748,051 dirtied=504
7. 42.664 95,116.143 ↓ 2,194.3 6,583 1

Nested Loop (cost=29.71..12,836.89 rows=3 width=622) (actual time=4.468..95,116.143 rows=6,583 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, us.name, us.email, r.name, cl.responsible,
  • Inner Unique: true
  • Buffers: shared hit=242,409 read=713,822 dirtied=454
8. 6,557.851 95,014.232 ↓ 6,583.0 19,749 1

Nested Loop (cost=29.56..12,829.10 rows=3 width=606) (actual time=3.988..95,014.232 rows=19,749 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, us.name, us.email, r.name, cl.respons
  • Join Filter: (oc.uuid = og.occurrence_uuid)
  • Buffers: shared hit=202,913 read=713,820 dirtied=454
  • -> Index Scan using idx_occurrence_group_occurrence_uuid on public.occurrence_group og (cost=0.57..213.16 rows=107 width=32) (actual time=0.981..0.990 rows=3 loops=
9. 20.775 88,456.381 ↓ 6,583.0 6,583 1

Nested Loop (cost=28.99..12,604.97 rows=1 width=606) (actual time=2.324..88,456.381 rows=6,583 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, tfv.occurrence_uuid, us.name, u
  • Inner Unique: true
  • Buffers: shared hit=184,122 read=699,568 dirtied=3
  • Output: og.group_uuid, og.occurrence_uuid
  • Index Cond: (og.occurrence_uuid = tfv.occurrence_uuid)
  • Buffers: shared hit=18,791 read=14,252 dirtied=451
10. 17.257 88,422.440 ↓ 6,583.0 6,583 1

Nested Loop (cost=28.85..12,603.69 rows=1 width=598) (actual time=2.312..88,422.440 rows=6,583 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, oc.classification_uuid, t
  • Inner Unique: true
  • Buffers: shared hit=170,956 read=699,568 dirtied=3
11. 727.325 88,378.851 ↓ 6,583.0 6,583 1

Nested Loop (cost=28.58..12,600.28 rows=1 width=603) (actual time=2.290..88,378.851 rows=6,583 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, oc.region_uuid, oc.
  • Buffers: shared hit=151,207 read=699,568 dirtied=3
  • -> Index Only Scan using idx_treatment_field_value_occurrence on public.treatment_field_value tfv (cost=0.42..4.30 rows=1 width=16) (actual time=0
12. 87,651.044 87,651.526 ↓ 287.7 6,616 1

Merge Join (cost=28.15..12,499.03 rows=23 width=587) (actual time=0.339..87,651.526 rows=6,616 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, oc.region_uui
  • Inner Unique: true
  • Merge Cond: (oc.user_uuid = us.uuid)
  • Buffers: shared hit=128,271 read=696,045
  • -> Index Scan using idx_occurrence_user_uuuid_state on public.occurrence oc (cost=0.57..1434865.38 rows=2,647 width=539) (actual time=0.027..
  • Output: oc.uuid, oc.version, oc.created_date, oc.last_updated_date, oc.occurrence_state, oc.entity_id, oc.entity_type, oc.occurrence_typ
  • Index Cond: (oc.user_uuid IS NOT NULL)
  • Filter: ((NOT oc.is_test) AND ((oc.entity_type)::text = 'ORDER'::text) AND ((oc.created_date - '03:00:00'::interval hour) >= (concat(dat
  • Rows Removed by Filter: 820,321
  • Buffers: shared hit=128,267 read=696,045
  • Output: tfv.occurrence_uuid
  • Index Cond: (tfv.occurrence_uuid = oc.uuid)
  • Heap Fetches: 6,584
  • Buffers: shared hit=22,936 read=3,523 dirtied=3
13. 0.352 0.482 ↑ 1.0 170 1

Sort (cost=27.59..28.02 rows=172 width=64) (actual time=0.307..0.482 rows=170 loops=1)

  • Output: us.name, us.email, us.uuid
  • Sort Key: us.uuid
  • Sort Method: quicksort Memory: 49kB
  • Buffers: shared hit=4
14. 0.130 0.130 ↑ 1.0 172 1

Seq Scan on public.users us (cost=0.00..21.20 rows=172 width=64) (actual time=0.021..0.130 rows=172 loops=1)

  • Output: us.name, us.email, us.uuid
  • Buffers: shared hit=4
15. 26.332 26.332 ↑ 1.0 1 6,583

Index Scan using pk_region on public.region r (cost=0.27..3.38 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=6,583)

  • Output: r.uuid, r.name, r.tenant, r.external_uuid
  • Index Cond: (r.uuid = oc.region_uuid)
  • Filter: ((r.tenant)::text = 'br'::text)
  • Buffers: shared hit=19,749
16. 13.166 13.166 ↑ 1.0 1 6,583

Index Scan using pk_classification on public.classification cl (cost=0.14..1.25 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=6,583)

  • Output: cl.uuid, cl.reason, cl.responsible, cl.occurrence_type
  • Index Cond: (cl.uuid = oc.classification_uuid)
  • Buffers: shared hit=13,166
17. 59.247 59.247 ↓ 0.0 0 19,749

Index Scan using pk_grouping on public."grouping" gr (cost=0.14..2.59 rows=1 width=48) (actual time=0.003..0.003 rows=0 loops=19,749)

  • Output: gr.uuid, gr.name, gr.type, gr.tenant
  • Index Cond: (gr.uuid = og.group_uuid)
  • Filter: ((gr.type)::text = 'LOGISTIC_OPERATOR'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=39,496 read=2
18. 6,381.175 14,568.179 ↑ 1.0 1 6,583

Nested Loop (cost=1.28..86.63 rows=1 width=40) (actual time=2.169..2.213 rows=1 loops=6,583)

  • Output: us_3.uuid, smt_2.created_date, smt_2.entity_id
  • Buffers: shared hit=306,404 read=34,229 dirtied=50
  • -> Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt_2 (cost=0.57..79.24 rows=1 width=24) (actual time=1.528..1.565 rows=1 loops=6
  • Output: smt_2.uuid, smt_2.created_date, smt_2.entity_id, smt_2.entity_type, smt_2.state_machine_id, smt_2.previous_state, smt_2.current_state, smt_2.event_id, smt_2.p
  • Index Cond: (oc.uuid = smt_2.entity_id)
  • Filter: (smt_2.created_date = (SubPlan 3))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=265,544 read=25,393 dirtied=50
19. 32.805 4,225.284 ↑ 1.0 1 6,561

Nested Loop (cost=0.71..7.29 rows=1 width=32) (actual time=0.640..0.644 rows=1 loops=6,561)

  • Output: us_3.uuid, o_2.uuid
  • Join Filter: (us_3.uuid = o_2.user_uuid)
  • Buffers: shared hit=40,860 read=8,836
20. 32.805 32.805 ↑ 1.0 1 6,561

Index Only Scan using pk_users on public.users us_3 (cost=0.14..2.75 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=6,561)

  • Output: us_3.uuid
  • Index Cond: (us_3.uuid = oc.user_uuid)
  • Heap Fetches: 6,565
  • Buffers: shared hit=13,125
21. 4,159.674 4,159.674 ↑ 1.0 1 6,561

Index Scan using pk_occurrence on public.occurrence o_2 (cost=0.57..4.44 rows=1 width=32) (actual time=0.633..0.634 rows=1 loops=6,561)

  • Output: o_2.uuid, o_2.version, o_2.created_date, o_2.last_updated_date, o_2.occurrence_state, o_2.entity_id, o_2.entity_type, o_2.occurrence_type, o_2.region_uu
  • Index Cond: (o_2.uuid = smt_2.entity_id)
  • Buffers: shared hit=27,735 read=8,836
22.          

SubPlan (for Nested Loop)

23. 3,961.720 3,961.720 ↑ 1.0 1 28,298

Aggregate (cost=13.13..13.23 rows=1 width=8) (actual time=0.140..0.140 rows=1 loops=28,298)

  • Output: max(smt2_2.created_date)
  • Buffers: shared hit=226,733 read=10,280 dirtied=11
  • -> Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt2_2 (cost=0.57..13.12 rows=3 width=8) (actual time=0.004..0.137
  • Output: smt2_2.uuid, smt2_2.created_date, smt2_2.entity_id, smt2_2.entity_type, smt2_2.state_machine_id, smt2_2.previous_state, smt2_2.current_state, sm
  • Index Cond: (smt2_2.entity_id = smt_2.entity_id)
  • Filter: ((smt2_2.current_state)::text = 'CONCLUDED'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=226,733 read=10,280 dirtied=11
24. 32.915 539.806 ↑ 1.0 1 6,583

Nested Loop (cost=1.28..86.60 rows=1 width=40) (actual time=0.048..0.082 rows=1 loops=6,583)

  • Output: us_2.uuid, smt_1.created_date, smt_1.entity_id
  • Buffers: shared hit=340,842 read=1
25. 125.166 408.146 ↑ 1.0 1 6,583

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt_1 (cost=0.57..79.21 rows=1 width=24) (actual time=0.034..0.062 rows=1 loops=6,583)

  • Output: smt_1.uuid, smt_1.created_date, smt_1.entity_id, smt_1.entity_type, smt_1.state_machine_id, smt_1.previous_state, smt_1.current_state, smt_1.event_id, smt_1.previou
  • Index Cond: (smt_1.entity_id = oc.uuid)
  • Filter: (smt_1.created_date = (SubPlan 2))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=290,937
26.          

SubPlan (for Index Scan)

27. 282.980 282.980 ↑ 1.0 1 28,298

Aggregate (cost=13.12..13.22 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=28,298)

  • Output: max(smt2_1.created_date)
  • Buffers: shared hit=237,013
  • -> Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt2_1 (cost=0.57..13.12 rows=1 width=8) (actual time=0.005..0.007 rows=1
  • Output: smt2_1.uuid, smt2_1.created_date, smt2_1.entity_id, smt2_1.entity_type, smt2_1.state_machine_id, smt2_1.previous_state, smt2_1.current_state, smt2_1.e
  • Index Cond: (smt2_1.entity_id = smt_1.entity_id)
  • Filter: ((smt2_1.current_state)::text = ANY ('{TREATED,CONCLUDING}'::text[]))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=237,013
28. 32.915 98.745 ↑ 1.0 1 6,583

Nested Loop (cost=0.71..7.29 rows=1 width=32) (actual time=0.011..0.015 rows=1 loops=6,583)

  • Output: us_2.uuid, o_1.uuid
  • Join Filter: (us_2.uuid = o_1.user_uuid)
  • Buffers: shared hit=49,905 read=1
29. 26.332 26.332 ↑ 1.0 1 6,583

Index Only Scan using pk_users on public.users us_2 (cost=0.14..2.75 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=6,583)

  • Output: us_2.uuid
  • Index Cond: (us_2.uuid = oc.user_uuid)
  • Heap Fetches: 6,587
  • Buffers: shared hit=13,169
30. 39.498 39.498 ↑ 1.0 1 6,583

Index Scan using pk_occurrence on public.occurrence o_1 (cost=0.57..4.44 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=6,583)

  • Output: o_1.uuid, o_1.version, o_1.created_date, o_1.last_updated_date, o_1.occurrence_state, o_1.entity_id, o_1.entity_type, o_1.occurrence_type, o_1.region_uuid, o_
  • Index Cond: (o_1.uuid = smt_1.entity_id)
  • Buffers: shared hit=36,736 read=1
31. 32.915 895.288 ↑ 1.0 1 6,583

Nested Loop (cost=1.28..86.60 rows=1 width=40) (actual time=0.056..0.136 rows=1 loops=6,583)

  • Output: us_1.uuid, smt.created_date, smt.entity_id
  • Buffers: shared hit=339,832 read=1,011
32. 98.834 381.814 ↑ 1.0 1 6,583

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt (cost=0.57..79.21 rows=1 width=24) (actual time=0.045..0.058 rows=1 loops=6,583)

  • Output: smt.uuid, smt.created_date, smt.entity_id, smt.entity_type, smt.state_machine_id, smt.previous_state, smt.current_state, smt.event_id, smt.previous_state_duration_seconds
  • Index Cond: (oc.uuid = smt.entity_id)
  • Filter: (smt.created_date = (SubPlan 1))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=290,937
33.          

SubPlan (for Index Scan)

34. 282.980 282.980 ↑ 1.0 1 28,298

Aggregate (cost=13.12..13.22 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=28,298)

  • Output: min(smt2.created_date)
  • Buffers: shared hit=237,013
  • -> Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt2 (cost=0.57..13.12 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=2
  • Output: smt2.uuid, smt2.created_date, smt2.entity_id, smt2.entity_type, smt2.state_machine_id, smt2.previous_state, smt2.current_state, smt2.event_id, smt2.previous
  • Index Cond: (smt2.entity_id = smt.entity_id)
  • Filter: ((smt2.current_state)::text = 'ALLOCATED'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=237,013
35. 26.332 480.559 ↑ 1.0 1 6,583

Nested Loop (cost=0.71..7.29 rows=1 width=32) (actual time=0.008..0.073 rows=1 loops=6,583)

  • Output: us_1.uuid, o.uuid
  • Join Filter: (us_1.uuid = o.user_uuid)
  • Buffers: shared hit=48,895 read=1,011
36. 19.749 19.749 ↑ 1.0 1 6,583

Index Only Scan using pk_users on public.users us_1 (cost=0.14..2.75 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=6,583)

  • Output: us_1.uuid
  • Index Cond: (us_1.uuid = oc.user_uuid)
  • Heap Fetches: 6,587
  • Buffers: shared hit=13,169
37. 434.478 434.478 ↑ 1.0 1 6,583

Index Scan using pk_occurrence on public.occurrence o (cost=0.57..4.44 rows=1 width=32) (actual time=0.004..0.066 rows=1 loops=6,583)

  • Output: o.uuid, o.version, o.created_date, o.last_updated_date, o.occurrence_state, o.entity_id, o.entity_type, o.occurrence_type, o.region_uuid, o.description_metadata, o.
  • Index Cond: (o.uuid = smt.entity_id)
  • Buffers: shared hit=35,726 read=1,011
Planning time : 27.870 ms
Execution time : 111,299.265 ms