explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zREn

Settings
# exclusive inclusive rows x rows loops node
1. 1.359 18,647.081 ↑ 1.0 1,000 1

Limit (cost=328,437.34..328,447.34 rows=1,000 width=276) (actual time=18,644.527..18,647.081 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, (CASE WHEN (d.bo_type = $2) THEN ((xpath('//ProjectNumber/text()'::text, bod.data, '{}'::text[] (...)
  • Buffers: shared hit=170498 read=163068, temp read=2933 written=2931
2.          

CTE resolution_ids

3. 38.215 94.402 ↑ 1.0 21,870 1

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

  • Output: r_1.id
  • Hash Cond: (rt_2.parent_id = r_1.id)
  • Buffers: shared hit=793
4. 17.330 17.330 ↑ 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.013..17.330 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. 19.045 38.857 ↑ 1.0 21,473 1

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

  • Output: r_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 1007kB
  • Buffers: shared hit=470
6. 19.812 19.812 ↑ 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.007..19.812 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. 262.620 18,645.722 ↑ 44.9 1,000 1

HashAggregate (cost=326,528.42..326,976.93 rows=44,851 width=276) (actual time=18,644.523..18,645.722 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, (CASE WHEN (d.bo_type = $2) THEN ((xpath('//ProjectNumber/text()'::text, bod.data, '{}':: (...)
  • 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, (CASE WHEN (d.bo_type = $2) THEN ((xpath('//ProjectNumber/text()'::text, bod.data, '{} (...)
  • Buffers: shared hit=170498 read=163068, temp read=2933 written=2931
8. 119.268 18,383.102 ↓ 2.0 87,649 1

Append (cost=162,948.28..324,510.12 rows=44,851 width=276) (actual time=3,837.414..18,383.102 rows=87,649 loops=1)

  • Buffers: shared hit=170498 read=163068, temp read=2933 written=2931
9. 5,849.915 11,585.472 ↓ 1.9 22,058 1

Hash Join (cost=162,948.28..242,328.29 rows=11,315 width=752) (actual time=3,837.412..11,585.472 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, CASE WHEN (d.bo_type = $2) THEN ((xpath('//ProjectNumber/text()'::text, bod.d (...)
  • Hash Cond: (r.id = resolution_ids.id)
  • Buffers: shared hit=79161 read=122349, temp read=2933 written=2931
10.          

Initplan (forHash Join)

11. 0.003 0.013 ↑ 1.0 1 1

Limit (cost=0.00..1.44 rows=1 width=2) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: t_business_object_types.id
  • Buffers: shared hit=1
12. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on public.t_business_object_types (cost=0.00..1.44 rows=1 width=2) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: t_business_object_types.id
  • Filter: ((t_business_object_types.typeof)::text = 'Integro.Objects.Modules.Docflow.DocflowObjects.ActReconciliation'::text)
  • Rows Removed by Filter: 16
  • Buffers: shared hit=1
13. 162.943 5,555.015 ↑ 1.0 22,068 1

Hash Join (cost=162,449.52..241,318.26 rows=22,630 width=784) (actual time=3,656.518..5,555.015 rows=22,068 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_limit, rt.parent_id, bor.data, bor.id, (...)
  • Hash Cond: (rt.parent_id = r.id)
  • Buffers: shared hit=78256 read=122349, temp read=2933 written=2931
14. 825.350 1,774.917 ↑ 1.0 21,887 1

Hash Join (cost=818.50..79,268.24 rows=22,022 width=107) (actual time=38.859..1,774.917 rows=21,887 loops=1)

  • Output: rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_limit, rt.parent_id, bort.data
  • Hash Cond: (bort.id = rt.id)
  • Buffers: shared hit=25870 read=40751
15. 910.795 910.795 ↓ 1.0 636,371 1

Seq Scan on public.t_docflow_businessobjects bort (cost=0.00..72,661.48 rows=636,348 width=48) (actual time=0.008..910.795 rows=636,371 loops=1)

  • 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
  • Buffers: shared hit=25547 read=40751
16. 20.073 38.772 ↑ 1.0 21,887 1

Hash (cost=543.22..543.22 rows=22,022 width=75) (actual time=38.772..38.772 rows=21,887 loops=1)

  • Output: rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_limit, rt.parent_id
  • Buckets: 4096 Batches: 1 Memory Usage: 1785kB
  • Buffers: shared hit=323
17. 18.699 18.699 ↑ 1.0 21,887 1

Seq Scan on public.t_resolution_task rt (cost=0.00..543.22 rows=22,022 width=75) (actual time=0.010..18.699 rows=21,887 loops=1)

  • Output: 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=323
18. 91.951 3,617.155 ↑ 1.0 21,667 1

Hash (cost=161,353.28..161,353.28 rows=22,220 width=677) (actual time=3,617.155..3,617.155 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, bor.data, bor.id, bot.typeof, d.bo_type, c.reg_number, bod.data
  • Buckets: 4096 Batches: 2 (originally 1) Memory Usage: 32769kB
  • Buffers: shared hit=52386 read=81598, temp written=2068
19. 35.655 3,525.204 ↑ 1.0 21,667 1

Hash Join (cost=82,596.03..161,353.28 rows=22,220 width=677) (actual time=1,838.340..3,525.204 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, bor.data, bor.id, bot.typeof, d.bo_type, c.reg_number, bod.data
  • Hash Cond: (r.bo_type = bot.id)
  • Buffers: shared hit=52386 read=81598
20. 795.583 3,489.479 ↑ 1.0 21,667 1

Hash Join (cost=82,594.24..161,045.96 rows=22,220 width=163) (actual time=1,838.254..3,489.479 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, bor.data, bor.id, d.bo_type, c.reg_number, bod.data
  • Hash Cond: (bor.id = r.id)
  • Buffers: shared hit=52385 read=81598
21. 855.702 855.702 ↓ 1.0 636,371 1

Seq Scan on public.t_docflow_businessobjects bor (cost=0.00..72,661.48 rows=636,348 width=48) (actual time=0.002..855.702 rows=636,371 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
  • Buffers: shared hit=25515 read=40783
22. 42.085 1,838.194 ↑ 1.0 21,667 1

Hash (cost=82,316.49..82,316.49 rows=22,220 width=115) (actual time=1,838.194..1,838.194 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, d.bo_type, c.reg_number, bod.data
  • Buckets: 4096 Batches: 1 Memory Usage: 18161kB
  • Buffers: shared hit=26870 read=40815
23. 735.256 1,796.109 ↑ 1.0 21,667 1

Hash Right Join (cost=3,864.77..82,316.49 rows=22,220 width=115) (actual time=203.437..1,796.109 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, d.bo_type, c.reg_number, bod.data
  • Hash Cond: (bod.id = d.id)
  • Buffers: shared hit=26870 read=40815
24. 858.442 858.442 ↓ 1.0 636,371 1

Seq Scan on public.t_docflow_businessobjects bod (cost=0.00..72,661.48 rows=636,348 width=48) (actual time=0.006..858.442 rows=636,371 loops=1)

  • Output: bod.id, bod.parent_id, bod.business_object_type, bod.typeof, bod.data, bod.user_id, bod.rec_date, bod.status, bod.del_rec
  • Buffers: shared hit=25483 read=40815
25. 21.005 202.411 ↑ 1.0 21,667 1

Hash (cost=3,587.02..3,587.02 rows=22,220 width=99) (actual time=202.411..202.411 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, d.bo_type, d.id, c.reg_number
  • Buckets: 4096 Batches: 1 Memory Usage: 2232kB
  • Buffers: shared hit=1387
26. 46.924 181.406 ↑ 1.0 21,667 1

Hash Right Join (cost=2,382.89..3,587.02 rows=22,220 width=99) (actual time=115.936..181.406 rows=21,667 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, d.bo_type, d.id, c.reg_number
  • Hash Cond: (c.parent_id = d.id)
  • Buffers: shared hit=1387
27. 18.605 18.605 ↑ 1.0 26,153 1

Seq Scan on public.t_card c (cost=0.00..752.36 rows=26,236 width=23) (actual time=0.005..18.605 rows=26,153 loops=1)

  • 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
  • Buffers: shared hit=490
28. 20.566 115.877 ↑ 1.0 21,482 1

Hash (cost=2,113.97..2,113.97 rows=21,514 width=92) (actual time=115.877..115.877 rows=21,482 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, d.bo_type, d.id
  • Buckets: 4096 Batches: 1 Memory Usage: 1919kB
  • Buffers: shared hit=897
29. 40.669 95.311 ↑ 1.0 21,482 1

Hash Left Join (cost=998.55..2,113.97 rows=21,514 width=92) (actual time=38.972..95.311 rows=21,482 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, d.bo_type, d.id
  • Hash Cond: (r.parent_id = d.id)
  • Buffers: shared hit=897
30. 15.715 15.715 ↑ 1.0 21,482 1

Seq Scan on public.t_resolution r (cost=0.00..685.14 rows=21,514 width=74) (actual time=0.005..15.715 rows=21,482 loops=1)

  • Output: r.id, r.parent_id, r.bo_type, r.rec_date, r.del_rec, r.parent_executer_id, r.parent_resolution_id, r.controlling, r.created_by, r.signer_workplace_id, r.signer_en (...)
  • Buffers: shared hit=470
31. 19.652 38.927 ↑ 1.0 25,064 1

Hash (cost=681.02..681.02 rows=25,402 width=18) (actual time=38.927..38.927 rows=25,064 loops=1)

  • Output: d.bo_type, d.id
  • Buckets: 4096 Batches: 1 Memory Usage: 1224kB
  • Buffers: shared hit=427
32. 19.275 19.275 ↑ 1.0 25,064 1

Seq Scan on public.t_document d (cost=0.00..681.02 rows=25,402 width=18) (actual time=0.007..19.275 rows=25,064 loops=1)

  • Output: d.bo_type, d.id
  • Buffers: shared hit=427
33. 0.034 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
34. 0.036 0.036 ↑ 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.036 rows=35 loops=1)

  • Output: bot.typeof, bot.id
  • Buffers: shared hit=1
35. 16.843 180.529 ↓ 107.4 21,471 1

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

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

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

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

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

  • Output: resolution_ids.id
  • Buffers: shared hit=793
38. 5,264.814 6,678.362 ↓ 2.0 65,591 1

Hash Join (cost=3,880.03..81,733.32 rows=33,536 width=116) (actual time=453.598..6,678.362 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, NULL::text, NULL::text, COALESCE(rt_1.prolongation_ (...)
  • Hash Cond: (borte.id = rte.id)
  • Buffers: shared hit=91337 read=40719
39. 960.147 960.147 ↓ 1.0 636,371 1

Seq Scan on public.t_docflow_businessobjects borte (cost=0.00..72,661.48 rows=636,348 width=48) (actual time=0.013..960.147 rows=636,371 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
  • Buffers: shared hit=25579 read=40719
40. 60.739 453.401 ↓ 2.0 65,591 1

Hash (cost=3,460.83..3,460.83 rows=33,536 width=84) (actual time=453.401..453.401 rows=65,591 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date, rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit
  • Buckets: 4096 Batches: 1 Memory Usage: 5835kB
  • Buffers: shared hit=65758
41. 128.253 392.662 ↓ 2.0 65,591 1

Hash Join (cost=710.89..3,460.83 rows=33,536 width=84) (actual time=219.897..392.662 rows=65,591 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date, rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit
  • Hash Cond: (rte.parent_id = rt_1.id)
  • Buffers: shared hit=65758
42. 44.544 44.544 ↑ 1.0 66,330 1

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

  • Output: rte.id, rte.parent_id, rte.bo_type, rte.rec_date, rte.del_rec, rte.index_in_task, rte.is_inner, rte.executer_id, rte.enterprise_id, rte.work_status, rte.complete_date, rte.svod
  • Buffers: shared hit=1157
43. 20.378 219.865 ↓ 2.0 21,873 1

Hash (cost=573.26..573.26 rows=11,011 width=40) (actual time=219.865..219.865 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
44. 61.086 199.487 ↓ 2.0 21,873 1

Nested Loop (cost=493.11..573.26 rows=11,011 width=40) (actual time=33.830..199.487 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
45. 37.951 52.517 ↓ 107.4 21,471 1

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

  • Output: resolution_ids_1.id
  • Group Key: resolution_ids_1.id
46. 14.566 14.566 ↑ 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.003..14.566 rows=21,870 loops=1)

  • Output: resolution_ids_1.id
47. 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.periodic (...)
  • Index Cond: (rt_1.parent_id = resolution_ids_1.id)
  • Buffers: shared hit=64601
Planning time : 8.416 ms
Execution time : 18,661.545 ms