explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rGLS

Settings
# exclusive inclusive rows x rows loops node
1. 1.206 23,048.006 ↑ 1.0 1,000 1

Limit (cost=125,866.31..125,876.31 rows=1,000 width=156) (actual time=23,045.557..23,048.006 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 = $0) THEN ((xpath('//ProjectNumber/text()'::text, bod.data, '{}'::text[] (...)
  • Buffers: shared hit=408211
2. 338.991 23,046.800 ↑ 6.8 1,000 1

HashAggregate (cost=125,866.31..125,934.05 rows=6,774 width=156) (actual time=23,045.554..23,046.800 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 = $0) 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 = $0) THEN ((xpath('//ProjectNumber/text()'::text, bod.data, '{} (...)
  • Buffers: shared hit=408211
3. 118.801 22,707.809 ↓ 12.9 87,639 1

Append (cost=1,429.67..125,426.00 rows=6,774 width=156) (actual time=48.408..22,707.809 rows=87,639 loops=1)

  • Buffers: shared hit=408211
4. 5,737.258 6,858.851 ↓ 882.2 22,055 1

Nested Loop Left Join (cost=1,429.67..49,173.50 rows=25 width=753) (actual time=48.405..6,858.851 rows=22,055 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 = $0) THEN ((xpath('//ProjectNumber/text()'::text, bod.d (...)
  • Buffers: shared hit=378956
5.          

Initplan (forNested Loop Left Join)

6. 0.003 0.012 ↑ 1.0 1 1

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

  • Output: t_business_object_types.id
  • Buffers: shared hit=1
7. 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
8. 68.025 1,012.231 ↓ 911.2 21,870 1

Nested Loop Left Join (cost=1,427.95..49,161.33 rows=24 width=762) (actual time=48.016..1,012.231 rows=21,870 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, bor.data, bort.data (...)
  • Buffers: shared hit=316339
9. 61.188 812.986 ↓ 911.2 21,870 1

Nested Loop Left Join (cost=1,427.52..49,046.22 rows=24 width=730) (actual time=48.005..812.986 rows=21,870 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, bor.data, bor (...)
  • Buffers: shared hit=232925
10. 48.089 642.448 ↓ 911.2 21,870 1

Hash Join (cost=1,427.24..49,036.11 rows=24 width=712) (actual time=47.994..642.448 rows=21,870 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, bor.dat (...)
  • Hash Cond: (r.bo_type = bot.id)
  • Buffers: shared hit=168249
11. 68.366 594.283 ↓ 911.2 21,870 1

Nested Loop (cost=1,425.45..49,033.99 rows=24 width=198) (actual time=47.904..594.283 rows=21,870 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=168248
12. 84.754 372.827 ↓ 31.1 21,870 1

Nested Loop (cost=1,425.02..45,502.30 rows=703 width=166) (actual time=47.888..372.827 rows=21,870 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=80364
13. 66.773 159.235 ↓ 31.1 21,473 1

Hash Join (cost=1,424.74..45,230.74 rows=690 width=123) (actual time=47.866..159.235 rows=21,473 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
14. 48.316 52.998 ↓ 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=8.360..52.998 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
15. 4.682 4.682 ↓ 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=4.682..4.682 rows=21,482 loops=1)

  • Index Cond: (bor.business_object_type = 100)
  • Buffers: shared hit=88
16. 19.037 39.464 ↑ 1.0 21,473 1

Hash (cost=685.14..685.14 rows=21,509 width=75) (actual time=39.464..39.464 rows=21,473 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: 1595kB
  • Buffers: shared hit=470
17. 20.427 20.427 ↑ 1.0 21,473 1

Seq Scan on public.t_resolution r (cost=0.00..685.14 rows=21,509 width=75) (actual time=0.010..20.427 rows=21,473 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
  • Filter: (NOT r.del_rec)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=470
18. 128.838 128.838 ↑ 1.0 1 21,473

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,473)

  • 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)
  • Filter: (NOT rt.del_rec)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=64606
19. 153.090 153.090 ↑ 1.0 1 21,870

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,870)

  • 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=87884
20. 0.037 0.076 ↑ 1.0 35 1

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

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

  • Output: bot.typeof, bot.id
  • Buffers: shared hit=1
22. 109.350 109.350 ↑ 1.0 1 21,870

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,870)

  • 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=64676
23. 131.220 131.220 ↑ 1.0 1 21,870

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.005..0.006 rows=1 loops=21,870)

  • 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=83414
24. 109.350 109.350 ↑ 1.0 1 21,870

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,870)

  • 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=62505
25. 15,071.399 15,730.157 ↓ 9.7 65,584 1

Hash Join (cost=5,917.90..76,184.77 rows=6,749 width=154) (actual time=202.790..15,730.157 rows=65,584 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: (rt_1.parent_id = r_1.id)
  • Buffers: shared hit=29255
26. 133.316 625.391 ↓ 9.7 65,649 1

Hash Join (cost=4,963.90..75,002.69 rows=6,784 width=170) (actual time=169.070..625.391 rows=65,649 loops=1)

  • Output: rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit, rt_1.parent_id, 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, (...)
  • Hash Cond: (rte.parent_id = rt_1.id)
  • Buffers: shared hit=28785
27. 207.538 456.250 ↓ 9.8 66,330 1

Hash Join (cost=4,145.44..74,057.02 rows=6,785 width=114) (actual time=133.179..456.250 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=28462
28. 121.687 131.169 ↓ 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.554..131.169 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=27305
29. 9.482 9.482 ↓ 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.482..9.482 rows=66,330 loops=1)

  • Index Cond: (borte.business_object_type = 102)
  • Buffers: shared hit=249
30. 62.247 117.543 ↑ 1.0 66,330 1

Hash (cost=1,827.71..1,827.71 rows=67,071 width=82) (actual time=117.543..117.543 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
31. 55.296 55.296 ↑ 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.296 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
32. 18.264 35.825 ↑ 1.0 21,883 1

Hash (cost=543.22..543.22 rows=22,019 width=56) (actual time=35.825..35.825 rows=21,883 loops=1)

  • Output: rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit, rt_1.parent_id
  • Buckets: 4096 Batches: 1 Memory Usage: 1450kB
  • Buffers: shared hit=323
33. 17.561 17.561 ↑ 1.0 21,883 1

Seq Scan on public.t_resolution_task rt_1 (cost=0.00..543.22 rows=22,019 width=56) (actual time=0.008..17.561 rows=21,883 loops=1)

  • Output: rt_1.id, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit, rt_1.parent_id
  • Filter: (NOT rt_1.del_rec)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=323
34. 16.242 33.367 ↑ 1.0 21,473 1

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

  • Output: r_1.id
  • Buckets: 4096 Batches: 1 Memory Usage: 1007kB
  • Buffers: shared hit=470
35. 17.125 17.125 ↑ 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..17.125 rows=21,473 loops=1)

  • Output: r_1.id
  • Filter: (NOT r_1.del_rec)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=470
Planning time : 7.709 ms
Execution time : 23,057.627 ms