explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mYKH

Settings
# exclusive inclusive rows x rows loops node
1. 43.797 2,520.967 ↑ 1.0 84,332 1

Unique (cost=107,612.89..110,796.00 rows=84,883 width=168) (actual time=2,463.470..2,520.967 rows=84,332 loops=1)

2. 155.633 2,477.170 ↓ 1.1 89,421 1

Sort (cost=107,612.89..107,825.10 rows=84,883 width=168) (actual time=2,463.469..2,477.170 rows=89,421 loops=1)

  • Sort Key: t.created_at DESC, t.id, t.transport_type_id, t.organization_id, t.status_id, t.tariff_type, t.updated_at, ((((r.data -> 0) ->> 'Departure'::text))::timestamp without time zone), (array_agg(event_orders.event_uid)), uact.user_id, user_actions.user_id, t.labels, (array_agg(oa.org_service_id)), (COALESCE((pa.value)::bigint, '180'::bigint))
  • Sort Method: external merge Disk: 17,208kB
3. 427.427 2,321.537 ↓ 1.1 89,421 1

Gather (cost=30,867.03..100,663.87 rows=84,883 width=168) (actual time=883.290..2,321.537 rows=89,421 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 76.330 1,894.110 ↑ 1.2 29,807 3 / 3

Merge Left Join (cost=29,867.03..90,432.84 rows=35,368 width=168) (actual time=809.321..1,894.110 rows=29,807 loops=3)

  • Merge Cond: (t.id = oa.order_id)
5. 65.526 1,810.427 ↑ 1.2 29,807 3 / 3

Nested Loop Left Join (cost=29,866.75..89,619.25 rows=35,368 width=766) (actual time=807.657..1,810.427 rows=29,807 loops=3)

6. 27.081 1,506.445 ↑ 1.2 29,807 3 / 3

Merge Join (cost=29,866.32..62,716.51 rows=35,368 width=733) (actual time=807.603..1,506.445 rows=29,807 loops=3)

  • Merge Cond: (eo.order_id = event_orders.order_id)
7. 54.043 1,273.073 ↑ 1.2 29,807 3 / 3

Merge Join (cost=29,866.02..55,253.04 rows=35,368 width=717) (actual time=786.737..1,273.073 rows=29,807 loops=3)

  • Merge Cond: (eo.order_id = r.order_id)
8. 44.144 1,117.817 ↑ 1.3 29,808 3 / 3

Merge Left Join (cost=29,865.73..44,765.22 rows=38,373 width=96) (actual time=786.525..1,117.817 rows=29,808 loops=3)

  • Merge Cond: (t.id = user_actions.entity_id)
9. 54.151 686.830 ↑ 1.3 29,808 3 / 3

Sort (cost=29,865.31..29,961.24 rows=38,373 width=88) (actual time=665.562..686.830 rows=29,808 loops=3)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 9,862kB
10. 70.070 632.679 ↑ 1.3 29,808 3 / 3

Hash Join (cost=15,222.31..26,943.63 rows=38,373 width=88) (actual time=380.674..632.679 rows=29,808 loops=3)

  • Hash Cond: (eo.order_id = t.id)
11. 103.799 182.529 ↑ 1.3 33,088 3 / 3

Merge Join (cost=1.17..11,181.66 rows=41,893 width=8) (actual time=0.092..182.529 rows=33,088 loops=3)

  • Merge Cond: ((e.uid)::text = (eo.event_uid)::text)
12. 42.105 42.105 ↑ 1.3 75,887 3 / 3

Parallel Index Only Scan using idx_views_events_v2_uid on events_v2 e (cost=0.42..7,624.32 rows=94,859 width=33) (actual time=0.036..42.105 rows=75,887 loops=3)

  • Heap Fetches: 0
13. 36.625 36.625 ↓ 1.0 100,526 3 / 3

Index Only Scan using event_orders_event_uid_order_id_key on event_orders eo (cost=0.42..2,650.14 rows=100,448 width=41) (actual time=0.043..36.625 rows=100,526 loops=3)

  • Heap Fetches: 0
14. 44.551 380.080 ↑ 1.0 85,008 3 / 3

Hash (cost=14,158.54..14,158.54 rows=85,008 width=80) (actual time=380.080..380.080 rows=85,008 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 10,964kB
15. 97.033 335.529 ↑ 1.0 85,008 3 / 3

Hash Right Join (cost=3,366.68..14,158.54 rows=85,008 width=80) (actual time=116.154..335.529 rows=85,008 loops=3)

  • Hash Cond: (uact.entity_id = t.id)
16. 124.075 124.075 ↓ 1.4 91,624 3 / 3

Seq Scan on user_actions uact (cost=0.00..10,619.71 rows=65,577 width=16) (actual time=0.029..124.075 rows=91,624 loops=3)

  • Filter: (((entity)::text = 'order'::text) AND ((action)::text = 'Order create'::text))
  • Rows Removed by Filter: 199,490
17. 59.885 114.421 ↑ 1.0 85,008 3 / 3

Hash (cost=2,304.08..2,304.08 rows=85,008 width=72) (actual time=114.421..114.421 rows=85,008 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 9,997kB
18. 54.536 54.536 ↑ 1.0 85,008 3 / 3

Seq Scan on order_v2 t (cost=0.00..2,304.08 rows=85,008 width=72) (actual time=0.025..54.536 rows=85,008 loops=3)

19. 52.400 386.843 ↓ 1.1 91,972 3 / 3

Unique (cost=0.42..13,290.33 rows=83,326 width=24) (actual time=0.029..386.843 rows=91,972 loops=3)

20. 334.443 334.443 ↑ 1.0 205,970 3 / 3

Index Scan using idx_user_actions_sort_entity_created on user_actions (cost=0.42..12,773.92 rows=206,565 width=24) (actual time=0.028..334.443 rows=205,970 loops=3)

  • Filter: ((NOT is_deleted) AND ((entity)::text = 'order'::text))
  • Rows Removed by Filter: 85,143
21. 101.213 101.213 ↓ 1.0 85,493 3 / 3

Index Scan using idx_route_order_id on route r (cost=0.29..9,827.60 rows=84,554 width=621) (actual time=0.024..101.213 rows=85,493 loops=3)

22. 134.065 206.291 ↓ 1.0 95,756 3 / 3

GroupAggregate (cost=0.29..5,861.31 rows=92,805 width=40) (actual time=0.083..206.291 rows=95,756 loops=3)

  • Group Key: event_orders.order_id
23. 72.226 72.226 ↑ 1.0 100,448 3 / 3

Index Scan using idx_event_orders_order_id on event_orders (cost=0.29..4,199.01 rows=100,448 width=41) (actual time=0.016..72.226 rows=100,448 loops=3)

24. 238.456 238.456 ↑ 1.0 1 89,421 / 3

Index Scan using idx_point_attribute_point_id on point_attribute pa (cost=0.43..0.75 rows=1 width=41) (actual time=0.006..0.008 rows=1 loops=89,421)

  • Index Cond: (point_id = (((r.data -> 0) ->> 'PointID'::text))::bigint)
  • Filter: ((key)::text = 'utc_offset'::text)
  • Rows Removed by Filter: 5
25. 4.727 7.353 ↓ 1.0 2,953 3 / 3

GroupAggregate (cost=0.28..145.68 rows=2,939 width=40) (actual time=0.030..7.353 rows=2,953 loops=3)

  • Group Key: oa.order_id
26. 2.626 2.626 ↑ 1.0 2,972 3 / 3

Index Scan using idx_order_additional_services_order_id on order_additional_services oa (cost=0.28..94.09 rows=2,972 width=16) (actual time=0.024..2.626 rows=2,972 loops=3)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 15
Planning time : 3.708 ms
Execution time : 2,528.639 ms