explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y5D2

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 0.164 ↓ 0.0 0 1

Sort (cost=100.47..100.47 rows=1 width=437) (actual time=0.163..0.164 rows=0 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.responsible, us.name, us.email, cl.reason, ((oc.description_metadata ->> 'ORIGIN_NAME'::text)), ((oc.description_metadata ->> 'DESTINATION_NAME'::text)), gr.name, ((oc.description_metadata ->> 'WORKER_NAME'::text)), gr.type, ((oc.description_metadata ->> 'REQUIRES_PAYMENT_MACHINE'::text)), (to_char((smt.created_date - oc.created_date), 'hh24:mi:ss'::text)), (to_char((smt_1.created_date - smt.created_date), 'hh24:mi:ss'::text)), (to_char((smt_2.created_date - smt_1.created_date), 'hh24:mi:ss'::text)), oc.entity_id, oc.occurrence_type, oc.created_date
  • Sort Key: ((oc.created_date - '03:00:00'::interval hour)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=36
2. 0.001 0.155 ↓ 0.0 0 1

Group (cost=100.39..100.46 rows=1 width=437) (actual time=0.155..0.155 rows=0 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.responsible, us.name, us.email, cl.reason, (oc.description_metadata ->> 'ORIGIN_NAME'::text), (oc.description_metadata ->> 'DESTINATION_NAME'::text), gr.name, (oc.description_metadata ->> 'WORKER_NAME'::text), gr.type, (oc.description_metadata ->> 'REQUIRES_PAYMENT_MACHINE'::text), (to_char((smt.created_date - oc.created_date), 'hh24:mi:ss'::text)), (to_char((smt_1.created_date - smt.created_date), 'hh24:mi:ss'::text)), (to_char((smt_2.created_date - smt_1.created_date), 'hh24:mi:ss'::text)), oc.entity_id, oc.occurrence_type, oc.created_date
  • 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'::text)), (to_char((smt_2.created_date - smt_1.created_date), 'hh24:mi:ss'::text)), gr.type, gr.name, (to_char((smt_2.created_date - oc.created_date), 'hh24:mi:ss'::text))
  • Buffers: shared hit=36
3. 0.028 0.154 ↓ 0.0 0 1

Sort (cost=100.39..100.39 rows=1 width=792) (actual time=0.154..0.154 rows=0 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_date), 'hh24:mi:ss'::text)), (to_char((smt_1.created_date - smt.created_date), 'hh24:mi:ss'::text)), (to_char((smt_2.created_date - smt_1.created_date), 'hh24:mi:ss'::text)), oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type
  • 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:ss'::text)), (to_char((smt_2.created_date - smt_1.created_date), 'hh24:mi:ss'::text)), gr.name, (to_char((smt_2.created_date - oc.created_date), 'hh24:mi:ss'::text))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=36
4. 0.001 0.126 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.43..100.38 rows=1 width=792) (actual time=0.126..0.126 rows=0 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_date), 'hh24:mi:ss'::text), to_char((smt_1.created_date - smt.created_date), 'hh24:mi:ss'::text), to_char((smt_2.created_date - smt_1.created_date), 'hh24:mi:ss'::text), oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type
  • Buffers: shared hit=36
5. 0.001 0.125 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.58..74.82 rows=1 width=696) (actual time=0.125..0.125 rows=0 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, gr.type, smt_2.created_date, smt_1.created_date
  • Buffers: shared hit=36
6. 0.001 0.124 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.72..49.28 rows=1 width=688) (actual time=0.124..0.124 rows=0 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, gr.type, smt_2.created_date
  • Buffers: shared hit=36
7. 0.001 0.123 ↓ 0.0 0 1

Nested Loop (cost=2.86..23.74 rows=1 width=680) (actual time=0.123..0.123 rows=0 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, gr.type
  • Join Filter: (oc.uuid = tfv.occurrence_uuid)
  • Buffers: shared hit=36
8. 0.001 0.122 ↓ 0.0 0 1

Nested Loop (cost=2.72..23.39 rows=1 width=696) (actual time=0.122..0.122 rows=0 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, og.occurrence_uuid, gr.name, gr.type
  • Inner Unique: true
  • Buffers: shared hit=36
9. 0.001 0.121 ↓ 0.0 0 1

Merge Join (cost=2.58..22.88 rows=2 width=683) (actual time=0.121..0.121 rows=0 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, og.occurrence_uuid, og.group_uuid
  • Inner Unique: true
  • Merge Cond: (oc.user_uuid = us.uuid)
  • Buffers: shared hit=36
10. 0.001 0.120 ↓ 0.0 0 1

Nested Loop (cost=1.15..41.48 rows=2 width=650) (actual time=0.119..0.120 rows=0 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, r.name, cl.responsible, cl.reason, og.occurrence_uuid, og.group_uuid
  • Buffers: shared hit=36
11. 0.001 0.119 ↓ 0.0 0 1

Nested Loop (cost=0.72..34.64 rows=1 width=618) (actual time=0.118..0.119 rows=0 loops=1)

  • Output: oc.uuid, oc.created_date, oc.last_updated_date, oc.description_metadata, oc.entity_id, oc.occurrence_type, oc.user_uuid, r.name, cl.responsible, cl.reason
  • Inner Unique: true
  • Buffers: shared hit=36
12. 0.001 0.118 ↓ 0.0 0 1

Nested Loop (cost=0.57..26.28 rows=1 width=610) (actual time=0.118..0.118 rows=0 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, r.name
  • Inner Unique: true
  • Buffers: shared hit=36
13. 0.117 0.117 ↓ 0.0 0 1

Index Scan using idx_occurrence_user_uuuid on public.occurrence oc (cost=0.43..21.98 rows=1 width=614) (actual time=0.116..0.117 rows=0 loops=1)

  • 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.region_uuid, oc.description_metadata, oc.user_uuid, oc.classification_uuid, oc.tenant, oc.is_test, oc.user_data, oc.is_allocable
  • 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() - '3 days'::interval day)), ' 00:00:00'))::timestamp without time zone) AND ((oc.created_date - '03:00:00'::interval hour) <= (concat(date((now() - '00:00:00'::interval day)), ' 23:59:59'))::timestamp without time zone))
  • Rows Removed by Filter: 39
  • Buffers: shared hit=36
14. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_region on public.region r (cost=0.14..4.25 rows=1 width=28) (never executed)

  • Output: r.uuid, r.name, r.tenant, r.external_uuid
  • Index Cond: (r.uuid = oc.region_uuid)
  • Filter: ((r.tenant)::text = 'br'::text)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_classification on public.classification cl (cost=0.14..4.25 rows=1 width=40) (never executed)

  • Output: cl.uuid, cl.reason, cl.responsible, cl.occurrence_type
  • Index Cond: (cl.uuid = oc.classification_uuid)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_occurrence_group_occurrence_uuid on public.occurrence_group og (cost=0.43..6.64 rows=2 width=32) (never executed)

  • Output: og.group_uuid, og.occurrence_uuid
  • Index Cond: (og.occurrence_uuid = oc.uuid)
17. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.44..1.45 rows=4 width=49) (never executed)

  • Output: us.name, us.email, us.uuid
  • Sort Key: us.uuid
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.users us (cost=0.00..1.40 rows=4 width=49) (never executed)

  • Output: us.name, us.email, us.uuid
19. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_grouping on public."grouping" gr (cost=0.14..0.25 rows=1 width=45) (never executed)

  • Output: gr.uuid, gr.name, gr.type, gr.tenant
  • Index Cond: (gr.uuid = og.group_uuid)
  • Filter: ((gr.type)::text = 'LOGISTIC_OPERATOR'::text)
20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_treatment_field_value_occurrence on public.treatment_field_value tfv (cost=0.14..0.25 rows=1 width=16) (never executed)

  • Output: tfv.occurrence_uuid
  • Index Cond: (tfv.occurrence_uuid = og.occurrence_uuid)
  • Heap Fetches: 0
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..25.44 rows=1 width=40) (never executed)

  • Output: us_3.uuid, smt_2.created_date, smt_2.entity_id
  • Inner Unique: true
  • Join Filter: (us_3.uuid = o_2.user_uuid)
22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..21.96 rows=1 width=40) (never executed)

  • Output: us_3.uuid, smt_2.created_date, smt_2.entity_id
  • Join Filter: (oc.user_uuid = us_3.uuid)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt_2 (cost=0.43..20.15 rows=1 width=24) (never executed)

  • 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.previous_state_duration_seconds
  • Index Cond: (oc.uuid = smt_2.entity_id)
  • Filter: (smt_2.created_date = (SubPlan 3))
24.          

SubPlan (for Index Scan)

25. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=6.65..6.75 rows=1 width=8) (never executed)

  • Output: max(smt2_2.created_date)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt2_2 (cost=0.43..6.65 rows=1 width=8) (never executed)

  • 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.event_id, smt2_2.previous_state_duration_seconds
  • Index Cond: (smt2_2.entity_id = smt_2.entity_id)
  • Filter: ((smt2_2.current_state)::text = 'CONCLUDED'::text)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.users us_3 (cost=0.00..1.40 rows=4 width=16) (never executed)

  • Output: us_3.uuid, us_3.name, us_3.role, us_3.email, us_3.application, us_3.tenant
28. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_occurrence on public.occurrence o_2 (cost=0.43..3.37 rows=1 width=32) (never executed)

  • 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_2.description_metadata, o_2.user_uuid, o_2.classification_uuid, o_2.tenant, o_2.is_test, o_2.user_data, o_2.is_allocable
  • Index Cond: (o_2.uuid = smt_2.entity_id)
29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..25.44 rows=1 width=40) (never executed)

  • Output: us_2.uuid, smt_1.created_date, smt_1.entity_id
  • Inner Unique: true
  • Join Filter: (us_2.uuid = o_1.user_uuid)
30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..21.96 rows=1 width=40) (never executed)

  • Output: us_2.uuid, smt_1.created_date, smt_1.entity_id
  • Join Filter: (oc.user_uuid = us_2.uuid)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt_1 (cost=0.43..20.15 rows=1 width=24) (never executed)

  • 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_state_duration_seconds
  • Index Cond: (smt_1.entity_id = oc.uuid)
  • Filter: (smt_1.created_date = (SubPlan 2))
32.          

SubPlan (for Index Scan)

33. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=6.65..6.75 rows=1 width=8) (never executed)

  • Output: max(smt2_1.created_date)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt2_1 (cost=0.43..6.65 rows=1 width=8) (never executed)

  • 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_id, smt2_1.previous_state_duration_seconds
  • Index Cond: (smt2_1.entity_id = smt_1.entity_id)
  • Filter: ((smt2_1.current_state)::text = ANY ('{TREATED,CONCLUDING}'::text[]))
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.users us_2 (cost=0.00..1.40 rows=4 width=16) (never executed)

  • Output: us_2.uuid, us_2.name, us_2.role, us_2.email, us_2.application, us_2.tenant
36. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_occurrence on public.occurrence o_1 (cost=0.43..3.37 rows=1 width=32) (never executed)

  • 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.description_metadata, o_1.user_uuid, o_1.classification_uuid, o_1.tenant, o_1.is_test, o_1.user_data, o_1.is_allocable
  • Index Cond: (o_1.uuid = smt_1.entity_id)
37. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..25.44 rows=1 width=40) (never executed)

  • Output: us_1.uuid, smt.created_date, smt.entity_id
  • Inner Unique: true
  • Join Filter: (us_1.uuid = o.user_uuid)
38. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..21.96 rows=1 width=40) (never executed)

  • Output: us_1.uuid, smt.created_date, smt.entity_id
  • Join Filter: (oc.user_uuid = us_1.uuid)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt (cost=0.43..20.15 rows=1 width=24) (never executed)

  • 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))
40.          

SubPlan (for Index Scan)

41. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=6.65..6.75 rows=1 width=8) (never executed)

  • Output: min(smt2.created_date)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_state_machine_trail_entity_id_type on public.state_machine_trail smt2 (cost=0.43..6.65 rows=1 width=8) (never executed)

  • 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_duration_seconds
  • Index Cond: (smt2.entity_id = smt.entity_id)
  • Filter: ((smt2.current_state)::text = 'ALLOCATED'::text)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.users us_1 (cost=0.00..1.40 rows=4 width=16) (never executed)

  • Output: us_1.uuid, us_1.name, us_1.role, us_1.email, us_1.application, us_1.tenant
44. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_occurrence on public.occurrence o (cost=0.43..3.37 rows=1 width=32) (never executed)

  • 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_uuid, o.classification_uuid, o.tenant, o.is_test, o.user_data, o.is_allocable
  • Index Cond: (o.uuid = smt.entity_id)
Planning time : 14.312 ms
Execution time : 0.581 ms