explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xIFa

Settings
# exclusive inclusive rows x rows loops node
1. 1.216 12,708.479 ↑ 1.0 1,000 1

Limit (cost=126,619.56..126,629.56 rows=1,000 width=118) (actual time=12,706.181..12,708.479 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=458765 read=14192
2.          

CTE resolution_ids

3. 38.640 94.769 ↑ 1.0 21,870 1

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

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

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

  • Output: r_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 1007kB
  • Buffers: shared hit=470
6. 19.729 19.729 ↑ 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.008..19.729 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. 268.461 12,707.263 ↑ 3.4 1,000 1

HashAggregate (cost=124,710.65..124,744.69 rows=3,404 width=118) (actual time=12,706.178..12,707.263 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=458765 read=14192
8. 118.608 12,438.802 ↓ 25.7 87,649 1

Append (cost=1,927.05..124,557.47 rows=3,404 width=118) (actual time=225.248..12,438.802 rows=87,649 loops=1)

  • Buffers: shared hit=458765 read=14192
9. 5,866.377 7,392.341 ↓ 1,838.2 22,058 1

Hash Join (cost=1,927.05..49,670.70 rows=12 width=752) (actual time=225.246..7,392.341 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=377254 read=2640
10.          

Initplan (forHash Join)

11. 0.004 0.013 ↑ 1.0 1 1

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

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

Seq Scan on public.t_business_object_types (cost=0.00..1.44 rows=1 width=2) (actual time=0.009..0.009 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. 64.327 1,344.885 ↓ 882.7 22,068 1

Nested Loop Left Join (cost=1,428.30..49,171.39 rows=25 width=784) (actual time=43.778..1,344.885 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, (...)
  • Buffers: shared hit=376349 read=2640
14. 76.425 1,149.260 ↓ 911.8 21,883 1

Nested Loop Left Join (cost=1,428.01..49,161.60 rows=24 width=793) (actual time=43.765..1,149.260 rows=21,883 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, b (...)
  • Buffers: shared hit=313830 read=2640
15. 72.432 854.005 ↓ 911.8 21,883 1

Nested Loop Left Join (cost=1,427.59..49,046.49 rows=24 width=761) (actual time=43.752..854.005 rows=21,883 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.d (...)
  • Buffers: shared hit=233031 read=4
16. 42.000 672.158 ↓ 911.8 21,883 1

Hash Join (cost=1,427.30..49,036.38 rows=24 width=743) (actual time=43.739..672.158 rows=21,883 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, (...)
  • Hash Cond: (r.bo_type = bot.id)
  • Buffers: shared hit=168324 read=4
17. 62.540 630.089 ↓ 911.8 21,883 1

Nested Loop (cost=1,425.51..49,034.26 rows=24 width=229) (actual time=43.645..630.089 rows=21,883 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, r.bo_type, rt.id, rt.task_number, rt.work_status, rt.exec_date, rt.prolongation_date, rt.outer_limit, rt.inner_lim (...)
  • Buffers: shared hit=168323 read=4
18. 79.575 392.485 ↓ 31.1 21,883 1

Nested Loop (cost=1,425.09..45,502.76 rows=703 width=197) (actual time=43.628..392.485 rows=21,883 loops=1)

  • Output: r.id, r.parent_id, r.parent_executer_id, r.parent_resolution_id, r.resolution_date, 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=80391
19. 68.435 162.536 ↓ 31.1 21,482 1

Hash Join (cost=1,424.80..45,230.82 rows=691 width=122) (actual time=43.606..162.536 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, bor.data, bor.id
  • Hash Cond: (bor.id = r.id)
  • Buffers: shared hit=15758
20. 53.327 56.926 ↓ 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.395..56.926 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
21. 3.599 3.599 ↓ 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.599..3.599 rows=21,482 loops=1)

  • Index Cond: (bor.business_object_type = 100)
  • Buffers: shared hit=88
22. 18.588 37.175 ↑ 1.0 21,482 1

Hash (cost=685.14..685.14 rows=21,514 width=74) (actual time=37.175..37.175 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
  • Buckets: 4096 Batches: 1 Memory Usage: 1554kB
  • Buffers: shared hit=470
23. 18.587 18.587 ↑ 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.008..18.587 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
  • Buffers: shared hit=470
24. 150.374 150.374 ↑ 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.006..0.007 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.peri (...)
  • Index Cond: (rt.parent_id = r.id)
  • Buffers: shared hit=64633
25. 175.064 175.064 ↑ 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.007..0.008 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=87932 read=4
26. 0.030 0.069 ↑ 1.0 35 1

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

  • Output: bot.typeof, bot.id
  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
  • Buffers: shared hit=1
27. 0.039 0.039 ↑ 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.014..0.039 rows=35 loops=1)

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

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

  • Output: d.id, d.parent_id, d.bo_type, d.rec_date, d.del_rec, d.is_rejected, d.created_by, d.creation_time, d.enterprise_id, d.is_private, d.folder_id, d.sender_id, d.is_duplicate
  • Index Cond: (d.id = r.parent_id)
  • Buffers: shared hit=64707
29. 218.830 218.830 ↑ 1.0 1 21,883

Index Scan using t_docflow_businessobjects_pkey on public.t_docflow_businessobjects bod (cost=0.42..4.79 rows=1 width=48) (actual time=0.009..0.010 rows=1 loops=21,883)

  • 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
  • Index Cond: ((d.id = bod.id) AND (bod.business_object_type = 1))
  • Buffers: shared hit=80799 read=2636
30. 131.298 131.298 ↑ 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.006 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
31. 16.814 181.066 ↓ 107.4 21,471 1

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

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

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

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

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

  • Output: resolution_ids.id
  • Buffers: shared hit=793
34. 3,892.168 4,927.853 ↓ 19.3 65,591 1

Hash Join (cost=4,856.34..74,852.72 rows=3,392 width=116) (actual time=478.214..4,927.853 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: (rte.parent_id = rt_1.id)
  • Buffers: shared hit=81511 read=11552
35. 202.448 813.862 ↓ 9.8 66,330 1

Hash Join (cost=4,145.44..74,057.02 rows=6,785 width=76) (actual time=256.149..813.862 rows=66,330 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date, borte.data
  • Hash Cond: (borte.id = rte.id)
  • Buffers: shared hit=16910 read=11552
36. 362.244 500.458 ↓ 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=145.122..500.458 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=15753 read=11552
37. 138.214 138.214 ↓ 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=138.214..138.214 rows=66,330 loops=1)

  • Index Cond: (borte.business_object_type = 102)
  • Buffers: shared hit=1 read=248
38. 56.031 110.956 ↑ 1.0 66,330 1

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

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date
  • Buckets: 8192 Batches: 1 Memory Usage: 4493kB
  • Buffers: shared hit=1157
39. 54.925 54.925 ↑ 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.010..54.925 rows=66,330 loops=1)

  • Output: rte.id, rte.parent_id, rte.work_status, rte.complete_date
  • Buffers: shared hit=1157
40. 20.490 221.823 ↓ 2.0 21,873 1

Hash (cost=573.26..573.26 rows=11,011 width=40) (actual time=221.823..221.823 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
41. 61.207 201.333 ↓ 2.0 21,873 1

Nested Loop (cost=493.11..573.26 rows=11,011 width=40) (actual time=35.429..201.333 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
42. 39.106 54.242 ↓ 107.4 21,471 1

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

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

  • Output: resolution_ids_1.id
44. 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