explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PfEF

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 2,078.712 ↑ 1.0 40 1

Limit (cost=176,200.98..176,202.88 rows=40 width=279) (actual time=2,078.657..2,078.712 rows=40 loops=1)

2. 0.045 2,078.706 ↑ 742.3 40 1

Unique (cost=176,200.98..177,611.30 rows=29,691 width=279) (actual time=2,078.655..2,078.706 rows=40 loops=1)

3. 154.971 2,078.661 ↑ 659.8 45 1

Sort (cost=176,200.98..176,275.21 rows=29,691 width=279) (actual time=2,078.653..2,078.661 rows=45 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)), ost.description, uact.user_id, ost.user_id, ((SubPlan 1)), (array_agg(order_carriages.carriage_id)), user_actions.user_id, t.labels, (array_agg(oa.org_service_id)), (COALESCE((pa.value)::bigint, '180'::bigint))
  • Sort Method: external merge Disk: 18,224kB
4. 256.881 1,923.690 ↓ 2.9 86,950 1

Merge Left Join (cost=96,145.47..173,995.27 rows=29,691 width=279) (actual time=756.419..1,923.690 rows=86,950 loops=1)

  • Merge Cond: (t.id = oa.order_id)
5. 41.948 1,574.659 ↓ 2.9 86,950 1

Merge Left Join (cost=96,145.19..140,862.94 rows=29,691 width=855) (actual time=755.287..1,574.659 rows=86,950 loops=1)

  • Merge Cond: (t.id = user_actions.entity_id)
6. 48.056 1,270.021 ↓ 2.9 86,950 1

Merge Left Join (cost=67,351.18..109,587.66 rows=29,691 width=847) (actual time=551.782..1,270.021 rows=86,950 loops=1)

  • Merge Cond: (t.id = uact.entity_id)
7. 57.860 1,125.523 ↓ 2.9 86,950 1

Nested Loop Left Join (cost=51,053.53..92,765.93 rows=29,691 width=839) (actual time=466.880..1,125.523 rows=86,950 loops=1)

  • Join Filter: (ost.order_id = t.id)
  • Rows Removed by Join Filter: 86,949
8. 151.360 1,067.663 ↓ 2.9 86,950 1

Gather Merge (cost=49,952.48..91,219.50 rows=29,691 width=784) (actual time=460.928..1,067.663 rows=86,950 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 12.958 916.303 ↓ 2.3 28,983 3 / 3

Merge Left Join (cost=48,952.46..86,792.40 rows=12,371 width=784) (actual time=452.764..916.303 rows=28,983 loops=3)

  • Merge Cond: (t.id = order_carriages.order_id)
10. 54.118 815.069 ↓ 2.3 28,983 3 / 3

Nested Loop Left Join (cost=48,952.04..76,040.10 rows=12,371 width=752) (actual time=452.188..815.069 rows=28,983 loops=3)

11. 26.979 616.034 ↓ 2.3 28,983 3 / 3

Merge Join (cost=48,951.61..58,460.94 rows=12,371 width=719) (actual time=452.150..616.034 rows=28,983 loops=3)

  • Merge Cond: (eo.order_id = event_orders.order_id)
12. 143.881 470.375 ↓ 2.3 28,983 3 / 3

Sort (cost=48,951.32..48,982.24 rows=12,371 width=703) (actual time=439.070..470.375 rows=28,983 loops=3)

  • Sort Key: t.id
  • Sort Method: external sort Disk: 24,264kB
13. 25.031 326.494 ↓ 2.3 28,983 3 / 3

Nested Loop (cost=11,328.38..48,110.42 rows=12,371 width=703) (actual time=169.519..326.494 rows=28,983 loops=3)

14. 32.348 214.511 ↓ 2.2 28,984 3 / 3

Hash Join (cost=11,328.09..39,185.50 rows=13,457 width=80) (actual time=169.475..214.511 rows=28,984 loops=3)

  • Hash Cond: ((e.uid)::text = (eo.event_uid)::text)
15. 14.646 17.023 ↑ 1.4 27,058 3 / 3

Parallel Bitmap Heap Scan on events_v2 e (cost=3,231.83..28,335.90 rows=37,411 width=33) (actual time=3.510..17.023 rows=27,058 loops=3)

  • Recheck Cond: (init_point_id = ANY ('{3337,31249392,77918439,50677385,33935738,45134433,5,31048375,33949580,45089315,14,20,10289611,89099974,44009457,25565607,45074279,45074285,31276267,31545800,3341,8,71904711,37534833,6,9851,3339,16,2,31289745,35633224,23381011,6041,11,9837,24,89099969,15564683,31289741,77918440,77918441,43994469,46242075,33935735,32893134,31289740,62771745,15,8269152,4,9790310,46242072,50661657,32879486,12,31048376,6821844,37534836,19944229,46242073,37534835,68403069,31276270,31289734,31276271,3088539,120065980,31559341,43949531,16932574,33935739,31559332,71904712,37520704,16932573,46242063,46242066,25,74404560,50677383,33935736,68727005,62379851,4746,27,74404559,33935733,46242070,31289737,19,7,31289739,37534832,1,23,18,89099975,74804035,23381010,71904713,33949582,89099978,77566940,72464520,33949581,3455,44009459,46242067,3340,21,16233,71904708,71904710,68727006,28,29570928,33935734,71904709,31289732,89099979,50708850,32879487,37534831,9790308,46257354,32879483,25565605,45074276,9,31559335,54901057,13,5683,16932572,25565606,46242069,74804036,11841881,10,4597,20721486,12124137,31276280,31289738,31559323,23393397,26,31048378,20721485,43964497,33935737,37534834,31262830,22,31222544,3,3338,46242071,45089314,31276281,5209,33234808,31545799,31303216,31048377,89099977,43994470,72464519,5255,25565604,17,45029225,89099973,31559339,3454,31249395,31289742,120062930,31249393,31559342,31559328,31559344}'::bigint[]))
  • Heap Blocks: exact=2,347
16. 2.377 2.377 ↑ 1.1 81,173 1 / 3

Bitmap Index Scan on idx_views_events_v2_init_point_id (cost=0.00..3,209.38 rows=89,786 width=0) (actual time=7.130..7.130 rows=81,173 loops=1)

  • Index Cond: (init_point_id = ANY ('{3337,31249392,77918439,50677385,33935738,45134433,5,31048375,33949580,45089315,14,20,10289611,89099974,44009457,25565607,45074279,45074285,31276267,31545800,3341,8,71904711,37534833,6,9851,3339,16,2,31289745,35633224,23381011,6041,11,9837,24,89099969,15564683,31289741,77918440,77918441,43994469,46242075,33935735,32893134,31289740,62771745,15,8269152,4,9790310,46242072,50661657,32879486,12,31048376,6821844,37534836,19944229,46242073,37534835,68403069,31276270,31289734,31276271,3088539,120065980,31559341,43949531,16932574,33935739,31559332,71904712,37520704,16932573,46242063,46242066,25,74404560,50677383,33935736,68727005,62379851,4746,27,74404559,33935733,46242070,31289737,19,7,31289739,37534832,1,23,18,89099975,74804035,23381010,71904713,33949582,89099978,77566940,72464520,33949581,3455,44009459,46242067,3340,21,16233,71904708,71904710,68727006,28,29570928,33935734,71904709,31289732,89099979,50708850,32879487,37534831,9790308,46257354,32879483,25565605,45074276,9,31559335,54901057,13,5683,16932572,25565606,46242069,74804036,11841881,10,4597,20721486,12124137,31276280,31289738,31559323,23393397,26,31048378,20721485,43964497,33935737,37534834,31262830,22,31222544,3,3338,46242071,45089314,31276281,5209,33234808,31545799,31303216,31048377,89099977,43994470,72464519,5255,25565604,17,45029225,89099973,31559339,3454,31249395,31289742,120062930,31249393,31559342,31559328,31559344}'::bigint[]))
17. 44.711 165.140 ↑ 1.0 89,405 3 / 3

Hash (cost=6,974.32..6,974.32 rows=89,755 width=113) (actual time=165.140..165.140 rows=89,405 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 14,383kB
18. 54.034 120.429 ↑ 1.0 89,405 3 / 3

Hash Join (cost=3,321.93..6,974.32 rows=89,755 width=113) (actual time=54.068..120.429 rows=89,405 loops=3)

  • Hash Cond: (eo.order_id = t.id)
19. 13.620 13.620 ↓ 1.0 100,756 3 / 3

Seq Scan on event_orders eo (cost=0.00..3,389.28 rows=100,228 width=41) (actual time=0.022..13.620 rows=100,756 loops=3)

20. 30.798 52.775 ↓ 1.0 85,302 3 / 3

Hash (cost=2,284.19..2,284.19 rows=83,019 width=72) (actual time=52.775..52.775 rows=85,302 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 10,033kB
21. 21.977 21.977 ↓ 1.0 85,302 3 / 3

Seq Scan on order_v2 t (cost=0.00..2,284.19 rows=83,019 width=72) (actual time=0.024..21.977 rows=85,302 loops=3)

22. 86.952 86.952 ↑ 1.0 1 86,952 / 3

Index Scan using idx_route_order_id on route r (cost=0.29..0.65 rows=1 width=623) (actual time=0.003..0.003 rows=1 loops=86,952)

  • Index Cond: (order_id = eo.order_id)
23. 82.388 118.680 ↓ 1.0 96,050 3 / 3

GroupAggregate (cost=0.29..8,165.23 rows=92,706 width=40) (actual time=0.067..118.680 rows=96,050 loops=3)

  • Group Key: event_orders.order_id
24. 36.292 36.292 ↓ 1.0 100,755 3 / 3

Index Scan using idx_event_orders_order_id on event_orders (cost=0.29..6,505.27 rows=100,228 width=41) (actual time=0.030..36.292 rows=100,755 loops=3)

25. 144.917 144.917 ↑ 1.0 1 86,950 / 3

Index Scan using idx_point_attribute_point_id on point_attribute pa (cost=0.43..1.41 rows=1 width=41) (actual time=0.004..0.005 rows=1 loops=86,950)

  • Index Cond: (point_id = (((r.data -> 0) ->> 'PointID'::text))::bigint)
  • Filter: ((key)::text = 'utc_offset'::text)
  • Rows Removed by Filter: 5
26. 34.269 88.276 ↓ 1.6 19,757 3 / 3

GroupAggregate (cost=0.42..10,551.35 rows=12,153 width=40) (actual time=0.057..88.276 rows=19,757 loops=3)

  • Group Key: order_carriages.order_id
27. 54.007 54.007 ↑ 1.0 131,630 3 / 3

Index Scan using order_carriages_order_id_carriage_id_uindex on order_carriages (cost=0.42..9,739.17 rows=132,054 width=16) (actual time=0.019..54.007 rows=131,630 loops=3)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 4,638
28. 0.000 0.000 ↑ 1.0 1 86,950

Materialize (cost=1,101.05..1,101.07 rows=1 width=63) (actual time=0.000..0.000 rows=1 loops=86,950)

29. 0.002 5.945 ↑ 1.0 1 1

Subquery Scan on ost (cost=1,101.05..1,101.06 rows=1 width=63) (actual time=5.945..5.945 rows=1 loops=1)

30. 0.001 5.943 ↑ 1.0 1 1

Limit (cost=1,101.05..1,101.05 rows=1 width=71) (actual time=5.943..5.943 rows=1 loops=1)

31. 1.181 5.942 ↑ 5,312.0 1 1

Sort (cost=1,101.05..1,114.33 rows=5,312 width=71) (actual time=5.942..5.942 rows=1 loops=1)

  • Sort Key: os.created_at DESC
  • Sort Method: top-N heapsort Memory: 25kB
32. 4.761 4.761 ↓ 1.0 5,331 1

Seq Scan on order_status_transition os (cost=0.00..1,074.49 rows=5,312 width=71) (actual time=0.014..4.761 rows=5,331 loops=1)

  • Filter: ((NOT is_deleted) AND (""to"" = 5))
  • Rows Removed by Filter: 21,847
33. 36.559 96.442 ↓ 1.5 96,581 1

Sort (cost=16,187.62..16,352.95 rows=66,130 width=16) (actual time=83.441..96.442 rows=96,581 loops=1)

  • Sort Key: uact.entity_id
  • Sort Method: quicksort Memory: 7,381kB
34. 59.883 59.883 ↓ 1.4 91,918 1

Seq Scan on user_actions uact (cost=0.00..10,892.92 rows=66,130 width=16) (actual time=0.008..59.883 rows=91,918 loops=1)

  • Filter: (((entity)::text = 'order'::text) AND ((action)::text = 'Order create'::text))
  • Rows Removed by Filter: 204,565
35. 28.635 262.690 ↓ 1.1 92,267 1

Unique (cost=28,794.01..29,848.27 rows=84,470 width=24) (actual time=199.720..262.690 rows=92,267 loops=1)

36. 159.982 234.055 ↑ 1.0 209,167 1

Sort (cost=28,794.01..29,321.14 rows=210,851 width=24) (actual time=199.718..234.055 rows=209,167 loops=1)

  • Sort Key: user_actions.entity_id, user_actions.created_at DESC
  • Sort Method: external merge Disk: 6,960kB
37. 74.073 74.073 ↑ 1.0 209,168 1

Seq Scan on user_actions (cost=0.00..10,148.60 rows=210,851 width=24) (actual time=0.013..74.073 rows=209,168 loops=1)

  • Filter: ((NOT is_deleted) AND ((entity)::text = 'order'::text))
  • Rows Removed by Filter: 87,315
38. 2.865 5.200 ↓ 1.0 2,964 1

GroupAggregate (cost=0.28..276.44 rows=2,945 width=40) (actual time=0.027..5.200 rows=2,964 loops=1)

  • Group Key: oa.order_id
39. 2.335 2.335 ↓ 1.0 2,978 1

Index Scan using idx_order_additional_services_order_id on order_additional_services oa (cost=0.28..224.77 rows=2,971 width=16) (actual time=0.018..2.335 rows=2,978 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 13
40.          

SubPlan (for Merge Left Join)

41. 86.950 86.950 ↓ 0.0 0 86,950

Seq Scan on dict_reasons (cost=0.00..1.09 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=86,950)

  • Filter: (id = ost.reason_id)
  • Rows Removed by Filter: 7
Planning time : 10.209 ms
Execution time : 2,092.590 ms