explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e5Yw

Settings
# exclusive inclusive rows x rows loops node
1. 12.615 780,542.252 ↓ 2,258.7 6,776 1

Group (cost=13,617.46..13,617.66 rows=3 width=451) (actual time=780,526.500..780,542.252 rows=6,776 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.responsibl
  • 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:ss'::t
  • Buffers: shared hit=1,706,597 read=305,246 dirtied=132 written=19
2. 45.193 780,529.637 ↓ 2,258.7 6,776 1

Sort (cost=13,617.46..13,617.46 rows=3 width=734) (actual time=780,526.489..780,529.637 rows=6,776 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.created_da
  • 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), 'hh24:mi:s
  • Sort Method: quicksort Memory: 7,087kB
  • Buffers: shared hit=1,706,597 read=305,246 dirtied=132 written=19
3. 63.379 780,484.444 ↓ 2,258.7 6,776 1

Nested Loop Left Join (cost=33.55..13,617.43 rows=3 width=734) (actual time=49.011..780,484.444 rows=6,776 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.created
  • Buffers: shared hit=1,706,597 read=305,246 dirtied=132 written=19
4. 27.810 776,572.297 ↓ 2,258.7 6,776 1

Nested Loop Left Join (cost=32.27..13,357.26 rows=3 width=638) (actual time=48.844..776,572.297 rows=6,776 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, gr.name
  • Buffers: shared hit=1,357,366 read=303,777 dirtied=131 written=19
5. 21.728 775,982.079 ↓ 2,258.7 6,776 1

Nested Loop Left Join (cost=30.99..13,097.15 rows=3 width=630) (actual time=48.741..775,982.079 rows=6,776 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=1,006,676 read=303,767 dirtied=131 written=19
6. 37.502 674,828.551 ↓ 2,258.7 6,776 1

Nested Loop (cost=29.71..12,836.96 rows=3 width=622) (actual time=15.511..674,828.551 rows=6,776 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
  • Inner Unique: true
  • Buffers: shared hit=693,747 read=266,625 dirtied=124 written=19
7. 45.507 674,709.737 ↓ 6,776.0 20,328 1

Nested Loop (cost=29.56..12,829.17 rows=3 width=606) (actual time=11.382..674,709.737 rows=20,328 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,
  • Join Filter: (oc.uuid = og.occurrence_uuid)
  • Buffers: shared hit=653,093 read=266,623 dirtied=124 written=19
8. 17.038 627,686.222 ↓ 6,776.0 6,776 1

Nested Loop (cost=28.99..12,605.04 rows=1 width=606) (actual time=3.722..627,686.222 rows=6,776 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, us.emai
  • Inner Unique: true
  • Buffers: shared hit=636,678 read=249,027 dirtied=3 written=19
9. 24.256 627,648.856 ↓ 6,776.0 6,776 1

Nested Loop (cost=28.85..12,603.76 rows=1 width=598) (actual time=3.712..627,648.856 rows=6,776 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, tfv.occ
  • Inner Unique: true
  • Buffers: shared hit=623,126 read=249,027 dirtied=3 written=19
10. 6,157.398 627,597.496 ↓ 6,776.0 6,776 1

Nested Loop (cost=28.58..12,600.35 rows=1 width=603) (actual time=3.693..627,597.496 rows=6,776 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.classi
  • Buffers: shared hit=602,798 read=249,027 dirtied=3 written=19
  • -> 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.896..
11. 621,439.662 621,440.098 ↓ 295.9 6,806 1

Merge Join (cost=28.15..12,499.10 rows=23 width=587) (actual time=0.313..621,440.098 rows=6,806 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.
  • Inner Unique: true
  • Merge Cond: (oc.user_uuid = us.uuid)
  • Buffers: shared hit=580,549 read=244,049 dirtied=2 written=19
  • -> Index Scan using idx_occurrence_user_uuuid_state on public.occurrence oc (cost=0.57..1434873.38 rows=2,647 width=539) (actual time=0.018..621423
  • Output: oc.uuid, oc.version, oc.created_date, oc.last_updated_date, oc.occurrence_state, oc.entity_id, oc.entity_type, oc.occurrence_type, oc.
  • 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(date((now
  • Rows Removed by Filter: 820,321
  • Buffers: shared hit=580,545 read=244,049 dirtied=2 written=19
  • Output: tfv.occurrence_uuid
  • Index Cond: (tfv.occurrence_uuid = oc.uuid)
  • Heap Fetches: 6,782
  • Buffers: shared hit=22,249 read=4,978 dirtied=1
12. 0.317 0.436 ↑ 1.0 170 1

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

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

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

  • Output: us.name, us.email, us.uuid
  • Buffers: shared hit=4
14. 27.104 27.104 ↑ 1.0 1 6,776

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,776)

  • 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=20,328
15. 20.328 20.328 ↑ 1.0 1 6,776

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

  • Output: cl.uuid, cl.reason, cl.responsible, cl.occurrence_type
  • Index Cond: (cl.uuid = oc.classification_uuid)
  • Buffers: shared hit=13,552
16. 46,978.008 46,978.008 ↑ 35.7 3 6,776

Index Scan using idx_occurrence_group_occurrence_uuid on public.occurrence_group og (cost=0.57..213.17 rows=107 width=32) (actual time=6.898..6.933 rows=3 loops=6,776)

  • Output: og.group_uuid, og.occurrence_uuid
  • Index Cond: (og.occurrence_uuid = tfv.occurrence_uuid)
  • Buffers: shared hit=16,415 read=17,596 dirtied=121
17. 81.312 81.312 ↓ 0.0 0 20,328

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

  • 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=40,654 read=2
18. 33.971 101,131.800 ↑ 1.0 1 6,776

Nested Loop (cost=1.28..86.63 rows=1 width=40) (actual time=14.885..14.925 rows=1 loops=6,776)

  • Output: us_3.uuid, smt_2.created_date, smt_2.entity_id
  • Buffers: shared hit=312,929 read=37,142 dirtied=7
19. 43,626.612 72,591.288 ↑ 1.0 1 6,776

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=10.678..10.713 rows=1 loops=6,776)

  • 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.previou
  • Index Cond: (oc.uuid = smt_2.entity_id)
  • Filter: (smt_2.created_date = (SubPlan 3))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=271,284 read=27,616 dirtied=7
20.          

SubPlan (for Index Scan)

21. 28,964.676 28,964.676 ↑ 1.0 1 29,081

Aggregate (cost=13.13..13.23 rows=1 width=8) (actual time=0.995..0.996 rows=1 loops=29,081)

  • Output: max(smt2_2.created_date)
  • Buffers: shared hit=231,437 read=11,993 dirtied=1
  • -> 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.992 rows=1
  • 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, smt2_2.e
  • 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=231,437 read=11,993 dirtied=1
22. 46.963 28,506.541 ↑ 1.0 1 6,709

Nested Loop (cost=0.71..7.29 rows=1 width=32) (actual time=4.245..4.249 rows=1 loops=6,709)

  • Output: us_3.uuid, o_2.uuid
  • Join Filter: (us_3.uuid = o_2.user_uuid)
  • Buffers: shared hit=41,645 read=9,526
23. 33.545 33.545 ↑ 1.0 1 6,709

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,709)

  • Output: us_3.uuid
  • Index Cond: (us_3.uuid = oc.user_uuid)
  • Heap Fetches: 6,713
  • Buffers: shared hit=13,420 read=1
24. 28,426.033 28,426.033 ↑ 1.0 1 6,709

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

  • 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_uuid, o_
  • Index Cond: (o_2.uuid = smt_2.entity_id)
  • Buffers: shared hit=28,225 read=9,525
25. 27.104 562.408 ↑ 1.0 1 6,776

Nested Loop (cost=1.28..86.60 rows=1 width=40) (actual time=0.053..0.083 rows=1 loops=6,776)

  • Output: us_2.uuid, smt_1.created_date, smt_1.entity_id
  • Buffers: shared hit=350,690 read=10
26. 122.526 413.336 ↑ 1.0 1 6,776

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.035..0.061 rows=1 loops=6,776)

  • 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.previous_stat
  • Index Cond: (smt_1.entity_id = oc.uuid)
  • Filter: (smt_1.created_date = (SubPlan 2))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=298,900
27.          

SubPlan (for Index Scan)

28. 290.810 290.810 ↑ 1.0 1 29,081

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

  • Output: max(smt2_1.created_date)
  • Buffers: shared hit=243,430
  • -> 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.004..0.007 rows=1 loops
  • 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.event_i
  • 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=243,430
29. 33.880 121.968 ↑ 1.0 1 6,776

Nested Loop (cost=0.71..7.29 rows=1 width=32) (actual time=0.014..0.018 rows=1 loops=6,776)

  • Output: us_2.uuid, o_1.uuid
  • Join Filter: (us_2.uuid = o_1.user_uuid)
  • Buffers: shared hit=51,790 read=10
30. 27.104 27.104 ↑ 1.0 1 6,776

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,776)

  • Output: us_2.uuid
  • Index Cond: (us_2.uuid = oc.user_uuid)
  • Heap Fetches: 6,780
  • Buffers: shared hit=13,555
31. 60.984 60.984 ↑ 1.0 1 6,776

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

  • 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_1.desc
  • Index Cond: (o_1.uuid = smt_1.entity_id)
  • Buffers: shared hit=38,235 read=10
32. 33.880 3,848.768 ↑ 1.0 1 6,776

Nested Loop (cost=1.28..86.60 rows=1 width=40) (actual time=0.053..0.568 rows=1 loops=6,776)

  • Output: us_1.uuid, smt.created_date, smt.entity_id
  • Buffers: shared hit=349,231 read=1,469 dirtied=1
33. 81.870 372.680 ↑ 1.0 1 6,776

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.042..0.055 rows=1 loops=6,776)

  • 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=298,900
34.          

SubPlan (for Index Scan)

35. 116.324 290.810 ↑ 1.0 1 29,081

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

  • Output: min(smt2.created_date)
  • Buffers: shared hit=243,430
36. 174.486 174.486 ↑ 1.0 1 29,081

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.006 rows=1 loops=29,081)

  • 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_state
  • Index Cond: (smt2.entity_id = smt.entity_id)
  • Filter: ((smt2.current_state)::text = 'ALLOCATED'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=243,430
37. 27.104 3,442.208 ↑ 1.0 1 6,776

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

  • Output: us_1.uuid, o.uuid
  • Join Filter: (us_1.uuid = o.user_uuid)
  • Buffers: shared hit=50,331 read=1,469 dirtied=1
38. 20.328 20.328 ↑ 1.0 1 6,776

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,776)

  • Output: us_1.uuid
  • Index Cond: (us_1.uuid = oc.user_uuid)
  • Heap Fetches: 6,780
  • Buffers: shared hit=13,555
39. 3,394.776 3,394.776 ↑ 1.0 1 6,776

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

  • 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.user_u
  • Index Cond: (o.uuid = smt.entity_id)
  • Buffers: shared hit=36,776 read=1,469 dirtied=1
Planning time : 64.734 ms
Execution time : 780,545.742 ms