explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nuop

Settings
# exclusive inclusive rows x rows loops node
1. 1.198 23,320.014 ↑ 1.0 1,000 1

Limit (cost=126,596.35..126,606.35 rows=1,000 width=156) (actual time=23,317.575..23,320.014 rows=1,000 loops=1)

  • Output: r.id, r.parent_id, rt.id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, rt.task_number, rt.work_status, rt.exec_date, c.reg_number, (COALESCE(rt.prolongation_date, rt.outer_limit, rt.inner_limit)), (CASE bot.typeof WHEN 'Integr (...)
  • Buffers: shared hit=389391 read=130
2.          

CTE resolution_ids

3. 36.512 91.943 ↑ 1.0 21,870 1

Hash Join (cost=954.00..1,908.92 rows=21,903 width=16) (actual time=38.574..91.943 rows=21,870 loops=1)

  • Output: r_1.id
  • Hash Cond: (rt_2.parent_id = r_1.id)
  • Buffers: shared hit=793
4. 16.897 16.897 ↑ 1.0 21,883 1

Seq Scan on public.t_resolution_task rt_2 (cost=0.00..543.22 rows=22,019 width=16) (actual time=0.009..16.897 rows=21,883 loops=1)

  • Output: rt_2.id, rt_2.parent_id, rt_2.bo_type, rt_2.rec_date, rt_2.del_rec, rt_2.outer_limit, rt_2.inner_limit, rt_2.outer_ex_limit, rt_2.prolongation_date, rt_2.svod_limit, rt_2.control_type, rt_2.periodicity, rt_2.period_end_date, rt_2.wo (...)
  • Filter: (NOT rt_2.del_rec)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=323
5. 18.715 38.534 ↑ 1.0 21,473 1

Hash (cost=685.14..685.14 rows=21,509 width=16) (actual time=38.534..38.534 rows=21,473 loops=1)

  • Output: r_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 1007kB
  • Buffers: shared hit=470
6. 19.819 19.819 ↑ 1.0 21,473 1

Seq Scan on public.t_resolution r_1 (cost=0.00..685.14 rows=21,509 width=16) (actual time=0.006..19.819 rows=21,473 loops=1)

  • Output: r_1.id
  • Filter: (NOT r_1.del_rec)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=470
7. 332.975 23,318.816 ↑ 3.4 1,000 1

HashAggregate (cost=124,687.43..124,721.47 rows=3,404 width=156) (actual time=23,317.572..23,318.816 rows=1,000 loops=1)

  • Output: r.id, r.parent_id, rt.id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, rt.task_number, rt.work_status, rt.exec_date, c.reg_number, (COALESCE(rt.prolongation_date, rt.outer_limit, rt.inner_limit)), (CASE bot.typeof WHEN ' (...)
  • Group Key: r.id, r.parent_id, rt.id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, rt.task_number, rt.work_status, rt.exec_date, c.reg_number, (COALESCE(rt.prolongation_date, rt.outer_limit, rt.inner_limit)), (CASE bot.typeof WHE (...)
  • Buffers: shared hit=389391 read=130
8. 120.108 22,985.841 ↓ 25.7 87,649 1

Append (cost=1,925.19..124,483.19 rows=3,404 width=156) (actual time=219.829..22,985.841 rows=87,649 loops=1)

  • Buffers: shared hit=389391 read=130
9. 5,657.569 6,818.088 ↓ 1,838.2 22,058 1

Hash Join (cost=1,925.19..49,554.03 rows=12 width=719) (actual time=219.827..6,818.088 rows=22,058 loops=1)

  • Output: r.id, r.parent_id, rt.id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, rt.task_number, rt.work_status, rt.exec_date, c.reg_number, COALESCE(rt.prolongation_date, rt.outer_limit, rt.inner_limit), CASE bot.type (...)
  • Hash Cond: (r.id = resolution_ids.id)
  • Buffers: shared hit=296458
10. 70.067 984.980 ↓ 882.7 22,068 1

Nested Loop Left Join (cost=1,427.87..49,056.28 rows=25 width=751) (actual time=43.902..984.980 rows=22,068 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_limit, rt.parent_id, bor.d (...)
  • Buffers: shared hit=295554
11. 66.154 805.498 ↓ 911.8 21,883 1

Nested Loop Left Join (cost=1,427.59..49,046.49 rows=24 width=760) (actual time=43.890..805.498 rows=21,883 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_limit, rt.parent_id, (...)
  • Buffers: shared hit=233035
12. 41.617 629.929 ↓ 911.8 21,883 1

Hash Join (cost=1,427.30..49,036.38 rows=24 width=744) (actual time=43.872..629.929 rows=21,883 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_limit, rt.pare (...)
  • Hash Cond: (r.bo_type = bot.id)
  • Buffers: shared hit=168328
13. 71.923 588.242 ↓ 911.8 21,883 1

Nested Loop (cost=1,425.51..49,034.26 rows=24 width=230) (actual time=43.774..588.242 rows=21,883 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, r.bo_type, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inn (...)
  • Buffers: shared hit=168327
14. 80.050 363.138 ↓ 31.1 21,883 1

Nested Loop (cost=1,425.09..45,502.76 rows=703 width=198) (actual time=43.758..363.138 rows=21,883 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, r.bo_type, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, (...)
  • Buffers: shared hit=80391
15. 64.411 154.196 ↓ 31.1 21,482 1

Hash Join (cost=1,424.80..45,230.82 rows=691 width=123) (actual time=43.736..154.196 rows=21,482 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, r.bo_type, bor.data, bor.id
  • Hash Cond: (bor.id = r.id)
  • Buffers: shared hit=15758
16. 48.866 52.601 ↓ 1.1 21,482 1

Bitmap Heap Scan on public.t_docflow_businessobjects bor (cost=470.73..44,091.11 rows=20,427 width=48) (actual time=6.515..52.601 rows=21,482 loops=1)

  • Output: bor.id, bor.parent_id, bor.business_object_type, bor.typeof, bor.data, bor.user_id, bor.rec_date, bor.status, bor.del_rec
  • Recheck Cond: (bor.business_object_type = 100)
  • Heap Blocks: exact=15200
  • Buffers: shared hit=15288
17. 3.735 3.735 ↓ 1.1 21,482 1

Bitmap Index Scan on t_docflow_businessobjects_business_object_type_idx (cost=0.00..465.63 rows=20,427 width=0) (actual time=3.735..3.735 rows=21,482 loops=1)

  • Index Cond: (bor.business_object_type = 100)
  • Buffers: shared hit=88
18. 18.401 37.184 ↑ 1.0 21,482 1

Hash (cost=685.14..685.14 rows=21,514 width=75) (actual time=37.184..37.184 rows=21,482 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, r.bo_type
  • Buckets: 4096 Batches: 1 Memory Usage: 1596kB
  • Buffers: shared hit=470
19. 18.783 18.783 ↑ 1.0 21,482 1

Seq Scan on public.t_resolution r (cost=0.00..685.14 rows=21,514 width=75) (actual time=0.008..18.783 rows=21,482 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.is_inner, r.bo_type
  • Buffers: shared hit=470
20. 128.892 128.892 ↑ 1.0 1 21,482

Index Scan using t_resolution_task_parent_id_idx on public.t_resolution_task rt (cost=0.29..0.38 rows=1 width=75) (actual time=0.005..0.006 rows=1 loops=21,482)

  • Output: rt.id, rt.parent_id, rt.bo_type, rt.rec_date, rt.del_rec, rt.outer_limit, rt.inner_limit, rt.outer_ex_limit, rt.prolongation_date, rt.svod_limit, rt.control_type, rt.periodicity, rt.period_end (...)
  • Index Cond: (rt.parent_id = r.id)
  • Buffers: shared hit=64633
21. 153.181 153.181 ↑ 1.0 1 21,883

Index Scan using t_docflow_businessobjects_pkey on public.t_docflow_businessobjects bort (cost=0.42..5.01 rows=1 width=48) (actual time=0.006..0.007 rows=1 loops=21,883)

  • Output: bort.id, bort.parent_id, bort.business_object_type, bort.typeof, bort.data, bort.user_id, bort.rec_date, bort.status, bort.del_rec
  • Index Cond: ((bort.id = rt.id) AND (bort.business_object_type = 101))
  • Buffers: shared hit=87936
22. 0.033 0.070 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=518) (actual time=0.070..0.070 rows=35 loops=1)

  • Output: bot.typeof, bot.id
  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
  • Buffers: shared hit=1
23. 0.037 0.037 ↑ 1.0 35 1

Seq Scan on public.t_business_object_types bot (cost=0.00..1.35 rows=35 width=518) (actual time=0.012..0.037 rows=35 loops=1)

  • Output: bot.typeof, bot.id
  • Buffers: shared hit=1
24. 109.415 109.415 ↑ 1.0 1 21,883

Index Only Scan using pkey_id on public.t_document d (cost=0.29..0.41 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=21,883)

  • Output: d.id
  • Index Cond: (d.id = r.parent_id)
  • Heap Fetches: 20763
  • Buffers: shared hit=64707
25. 109.415 109.415 ↑ 1.0 1 21,883

Index Scan using t_card_parent_id_idx on public.t_card c (cost=0.29..0.40 rows=1 width=23) (actual time=0.004..0.005 rows=1 loops=21,883)

  • Output: c.id, c.parent_id, c.bo_type, c.rec_date, c.del_rec, c.reg_number, c.reg_date, c.recipients_employee, c.recipients_enterprise
  • Index Cond: (d.id = c.parent_id)
  • Buffers: shared hit=62519
26. 16.407 175.539 ↓ 107.4 21,471 1

Hash (cost=494.82..494.82 rows=200 width=16) (actual time=175.539..175.539 rows=21,471 loops=1)

  • Output: resolution_ids.id
  • Buckets: 1024 Batches: 1 Memory Usage: 1007kB
  • Buffers: shared hit=793
27. 36.193 159.132 ↓ 107.4 21,471 1

HashAggregate (cost=492.82..494.82 rows=200 width=16) (actual time=143.397..159.132 rows=21,471 loops=1)

  • Output: resolution_ids.id
  • Group Key: resolution_ids.id
  • Buffers: shared hit=793
28. 122.939 122.939 ↑ 1.0 21,870 1

CTE Scan on resolution_ids (cost=0.00..438.06 rows=21,903 width=16) (actual time=38.578..122.939 rows=21,870 loops=1)

  • Output: resolution_ids.id
  • Buffers: shared hit=793
29. 15,200.840 16,047.645 ↓ 19.3 65,591 1

Hash Join (cost=4,856.34..74,895.12 rows=3,392 width=154) (actual time=347.025..16,047.645 rows=65,591 loops=1)

  • Output: rte.id, rte.parent_id, rt_1.id, NULL::uuid, NULL::uuid, NULL::timestamp without time zone, NULL::character varying, rte.work_status, rte.complete_date, NULL::character varying, COALESCE(rt_1.prolongation_date, rt_1.outer_limit, (...)
  • Hash Cond: (rte.parent_id = rt_1.id)
  • Buffers: shared hit=92933 read=130
30. 208.712 632.205 ↓ 9.8 66,330 1

Hash Join (cost=4,145.44..74,057.02 rows=6,785 width=114) (actual time=132.045..632.205 rows=66,330 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date, rte.is_inner, rte.index_in_task, rte.executer_id, rte.enterprise_id, rte.svod, borte.data
  • Hash Cond: (borte.id = rte.id)
  • Buffers: shared hit=28332 read=130
31. 297.577 306.924 ↓ 1.0 66,330 1

Bitmap Heap Scan on public.t_docflow_businessobjects borte (cost=1,479.35..70,518.36 rows=64,377 width=48) (actual time=15.403..306.924 rows=66,330 loops=1)

  • Output: borte.id, borte.parent_id, borte.business_object_type, borte.typeof, borte.data, borte.user_id, borte.rec_date, borte.status, borte.del_rec
  • Recheck Cond: (borte.business_object_type = 102)
  • Heap Blocks: exact=27056
  • Buffers: shared hit=27175 read=130
32. 9.347 9.347 ↓ 1.0 66,330 1

Bitmap Index Scan on t_docflow_businessobjects_business_object_type_idx (cost=0.00..1,463.25 rows=64,377 width=0) (actual time=9.347..9.347 rows=66,330 loops=1)

  • Index Cond: (borte.business_object_type = 102)
  • Buffers: shared hit=249
33. 61.561 116.569 ↑ 1.0 66,330 1

Hash (cost=1,827.71..1,827.71 rows=67,071 width=82) (actual time=116.569..116.569 rows=66,330 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date, rte.is_inner, rte.index_in_task, rte.executer_id, rte.enterprise_id, rte.svod
  • Buckets: 8192 Batches: 1 Memory Usage: 7604kB
  • Buffers: shared hit=1157
34. 55.008 55.008 ↑ 1.0 66,330 1

Seq Scan on public.t_resolution_task_executer rte (cost=0.00..1,827.71 rows=67,071 width=82) (actual time=0.010..55.008 rows=66,330 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date, rte.is_inner, rte.index_in_task, rte.executer_id, rte.enterprise_id, rte.svod
  • Buffers: shared hit=1157
35. 20.208 214.600 ↓ 2.0 21,873 1

Hash (cost=573.26..573.26 rows=11,011 width=40) (actual time=214.600..214.600 rows=21,873 loops=1)

  • Output: rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit
  • Buckets: 2048 Batches: 1 Memory Usage: 1107kB
  • Buffers: shared hit=64601
36. 57.385 194.392 ↓ 2.0 21,873 1

Nested Loop (cost=493.11..573.26 rows=11,011 width=40) (actual time=33.033..194.392 rows=21,873 loops=1)

  • Output: rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit
  • Buffers: shared hit=64601
37. 36.890 51.123 ↓ 107.4 21,471 1

HashAggregate (cost=492.82..494.82 rows=200 width=16) (actual time=33.006..51.123 rows=21,471 loops=1)

  • Output: resolution_ids_1.id
  • Group Key: resolution_ids_1.id
38. 14.233 14.233 ↑ 1.0 21,870 1

CTE Scan on resolution_ids resolution_ids_1 (cost=0.00..438.06 rows=21,903 width=16) (actual time=0.002..14.233 rows=21,870 loops=1)

  • Output: resolution_ids_1.id
39. 85.884 85.884 ↑ 1.0 1 21,471

Index Scan using t_resolution_task_parent_id_idx on public.t_resolution_task rt_1 (cost=0.29..0.38 rows=1 width=56) (actual time=0.003..0.004 rows=1 loops=21,471)

  • Output: rt_1.id, rt_1.parent_id, rt_1.bo_type, rt_1.rec_date, rt_1.del_rec, rt_1.outer_limit, rt_1.inner_limit, rt_1.outer_ex_limit, rt_1.prolongation_date, rt_1.svod_limit, rt_1.control_type, rt_1.periodicity, rt_1.pe (...)
  • Index Cond: (rt_1.parent_id = resolution_ids_1.id)
  • Buffers: shared hit=64601