explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FYK

Settings
# exclusive inclusive rows x rows loops node
1. 339.387 203,921.930 ↓ 6.9 15,275 1

Hash Join (cost=6.59..399,154.00 rows=2,216 width=677) (actual time=0.507..203,921.930 rows=15,275 loops=1)

  • Output: d.id, d.parent_id, d.del_rec, CASE dbot.typeof WHEN 'Integro.Objects.Modules.Docflow.DocflowObjects.IncomingDocument'::text THEN 2 WHEN 'Integro.Objects.Modules.Docflow.DocflowObjects.OutgoingDocument'::text THEN 3 WHEN 'Integro.Objects.Modules.D (...)
  • Hash Cond: (c.bo_type = cbot.id)
  • Buffers: shared hit=525066 read=44265
2. 61.569 528.406 ↓ 6.9 15,275 1

Nested Loop (cost=4.80..2,984.10 rows=2,216 width=679) (actual time=0.151..528.406 rows=15,275 loops=1)

  • Output: d.id, d.parent_id, d.del_rec, d.is_duplicate, d.created_by, c.reg_number, c.reg_date, c.id, c.recipients_employee, c.recipients_enterprise, c.bo_type, dbot.typeof, read.business_object_id, fav.business_object_id
  • Buffers: shared hit=113293 read=4
3. 36.581 316.997 ↓ 8.7 18,730 1

Hash Left Join (cost=4.51..2,095.39 rows=2,146 width=598) (actual time=0.138..316.997 rows=18,730 loops=1)

  • Output: d.id, d.parent_id, d.del_rec, d.is_duplicate, d.created_by, dbot.typeof, read.business_object_id, fav.business_object_id
  • Hash Cond: (d.id = fav.business_object_id)
  • Buffers: shared hit=56963 read=4
4. 59.339 280.341 ↓ 8.7 18,730 1

Nested Loop Left Join (cost=1.94..2,082.05 rows=2,146 width=582) (actual time=0.053..280.341 rows=18,730 loops=1)

  • Output: d.id, d.parent_id, d.del_rec, d.is_duplicate, d.created_by, dbot.typeof, read.business_object_id
  • Buffers: shared hit=56962 read=4
5. 50.156 89.892 ↓ 8.7 18,730 1

Hash Join (cost=1.52..796.27 rows=2,146 width=566) (actual time=0.032..89.892 rows=18,730 loops=1)

  • Output: d.id, d.parent_id, d.del_rec, d.is_duplicate, d.created_by, dbot.typeof
  • Hash Cond: (d.bo_type = dbot.id)
  • Buffers: shared hit=427
6. 39.719 39.719 ↑ 1.0 24,729 1

Seq Scan on public.t_document d (cost=0.00..679.42 rows=25,031 width=52) (actual time=0.007..39.719 rows=24,729 loops=1)

  • 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
  • Filter: ((NOT d.is_duplicate) OR d.del_rec)
  • Rows Removed by Filter: 305
  • Buffers: shared hit=426
7. 0.004 0.017 ↑ 1.0 3 1

Hash (cost=1.48..1.48 rows=3 width=518) (actual time=0.017..0.017 rows=3 loops=1)

  • Output: dbot.typeof, dbot.id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
8. 0.013 0.013 ↑ 1.0 3 1

Seq Scan on public.t_business_object_types dbot (cost=0.00..1.48 rows=3 width=518) (actual time=0.005..0.013 rows=3 loops=1)

  • Output: dbot.typeof, dbot.id
  • Filter: ((dbot.typeof)::text = ANY ('{Integro.Objects.Modules.Docflow.DocflowObjects.IncomingDocument,Integro.Objects.Modules.Docflow.DocflowObjects.OutgoingDocument,Integro.Objects.Modules.Docflow.DocflowObjects.Sluzh (...)
  • Rows Removed by Filter: 32
  • Buffers: shared hit=1
9. 131.110 131.110 ↓ 0.0 0 18,730

Index Only Scan using l_readers_pkey on public.l_readers read (cost=0.42..0.59 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=18,730)

  • Output: read.business_object_id, read.employee_id
  • Index Cond: ((read.business_object_id = d.id) AND (read.employee_id = 'a5098b77-8081-42d4-b912-10a83e086747'::uuid))
  • Heap Fetches: 234
  • Buffers: shared hit=56535 read=4
10. 0.035 0.075 ↓ 1.0 45 1

Hash (cost=2.03..2.03 rows=44 width=16) (actual time=0.075..0.075 rows=45 loops=1)

  • Output: fav.business_object_id
  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
  • Buffers: shared hit=1
11. 0.040 0.040 ↓ 1.0 45 1

Seq Scan on public.l_favorites fav (cost=0.00..2.03 rows=44 width=16) (actual time=0.007..0.040 rows=45 loops=1)

  • Output: fav.business_object_id
  • Filter: (fav.employee_id = 'a5098b77-8081-42d4-b912-10a83e086747'::uuid)
  • Rows Removed by Filter: 43
  • Buffers: shared hit=1
12. 149.840 149.840 ↑ 1.0 1 18,730

Index Scan using t_card_parent_id_idx on public.t_card c (cost=0.29..0.40 rows=1 width=97) (actual time=0.007..0.008 rows=1 loops=18,730)

  • 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: (c.parent_id = d.id)
  • Filter: ((NOT c.del_rec) AND ((c.reg_number)::text <> ''::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=56330
13. 0.028 0.060 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=2) (actual time=0.060..0.060 rows=35 loops=1)

  • Output: cbot.id
  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
  • Buffers: shared hit=1
14. 0.032 0.032 ↑ 1.0 35 1

Seq Scan on public.t_business_object_types cbot (cost=0.00..1.35 rows=35 width=2) (actual time=0.008..0.032 rows=35 loops=1)

  • Output: cbot.id
  • Buffers: shared hit=1
15.          

SubPlan (forHash Join)

16. 86,742.063 86,742.063 ↑ 1.0 1 11,673

Index Scan using t_properties_business_object_id_property_idx on public.t_properties (cost=0.56..8.58 rows=1 width=29) (actual time=7.417..7.431 rows=1 loops=11,673)

  • Output: t_properties.property_value
  • Index Cond: ((t_properties.business_object_id = c.id) AND ((t_properties.property)::text = 'Correspondent'::text))
  • Buffers: shared hit=45606 read=12877
17. 1,260.684 1,260.684 ↓ 0.0 0 11,673

Index Scan using t_properties_business_object_id_property_idx on public.t_properties t_properties_1 (cost=0.56..8.58 rows=1 width=29) (actual time=0.100..0.108 rows=0 loops=11,673)

  • Output: t_properties_1.property_value
  • Index Cond: ((t_properties_1.business_object_id = c.id) AND ((t_properties_1.property)::text = 'IsSection'::text))
  • Buffers: shared hit=51640 read=666
18. 0.030 0.030 ↑ 1.0 1 3

Index Scan using t_properties_business_object_id_property_idx on public.t_properties t_properties_2 (cost=0.56..8.58 rows=1 width=29) (actual time=0.009..0.010 rows=1 loops=3)

  • Output: t_properties_2.property_value
  • Index Cond: ((t_properties_2.business_object_id = c.id) AND ((t_properties_2.property)::text = 'SectionNumber'::text))
  • Buffers: shared hit=15
19. 84,883.175 84,883.175 ↑ 1.0 1 15,275

Index Scan using t_properties_business_object_id_property_idx on public.t_properties t_properties_3 (cost=0.56..8.58 rows=1 width=29) (actual time=5.544..5.557 rows=1 loops=15,275)

  • Output: t_properties_3.property_value
  • Index Cond: ((t_properties_3.business_object_id = d.id) AND ((t_properties_3.property)::text = 'Theme'::text))
  • Buffers: shared hit=60441 read=16092
20. 28,839.200 28,839.200 ↑ 7.0 1 15,275

Index Scan using t_docflow_businessobjects_parent_id_idx on public.t_docflow_businessobjects (cost=0.42..52.75 rows=7 width=0) (actual time=1.888..1.888 rows=1 loops=15,275)

  • Index Cond: (t_docflow_businessobjects.parent_id = d.id)
  • Filter: ((NOT t_docflow_businessobjects.del_rec) AND (t_docflow_businessobjects.business_object_type <> ALL ('{2,101,102,103,400,1000,1100,5002}'::integer[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=54408 read=14626
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.t_docflow_businessobjects t_docflow_businessobjects_1 (cost=0.00..79,018.98 rows=369,780 width=16) (never executed)

  • Output: t_docflow_businessobjects_1.parent_id
  • Filter: ((NOT t_docflow_businessobjects_1.del_rec) AND (t_docflow_businessobjects_1.business_object_type <> ALL ('{2,101,102,103,400,1000,1100,5002}'::integer[])))
22. 61.100 809.575 ↓ 0.0 0 15,275

Limit (cost=68.28..68.28 rows=1 width=9) (actual time=0.053..0.053 rows=0 loops=15,275)

  • Output: control_date_sub_query.controldate, control_date_sub_query.executed
  • Buffers: shared hit=99831
23. 106.925 748.475 ↓ 0.0 0 15,275

Sort (cost=68.28..68.28 rows=2 width=9) (actual time=0.049..0.049 rows=0 loops=15,275)

  • Output: control_date_sub_query.controldate, control_date_sub_query.executed
  • Sort Key: control_date_sub_query.executed
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=99831
24. 76.375 641.550 ↓ 0.0 0 15,275

Subquery Scan on control_date_sub_query (cost=68.22..68.27 rows=2 width=9) (actual time=0.041..0.042 rows=0 loops=15,275)

  • Output: control_date_sub_query.controldate, control_date_sub_query.executed
  • Buffers: shared hit=99831
25. 45.825 565.175 ↓ 0.0 0 15,275

HashAggregate (cost=68.22..68.25 rows=2 width=28) (actual time=0.037..0.037 rows=0 loops=15,275)

  • Output: ((rt.work_status = ANY ('{3,6,14}'::integer[]))), min(COALESCE(rt.prolongation_date, rt.outer_limit, rt.inner_limit))
  • Group Key: (rt.work_status = ANY ('{3,6,14}'::integer[]))
  • Buffers: shared hit=99831
26. 180.919 519.350 ↓ 0.0 0 15,275

Nested Loop (cost=4.59..68.21 rows=2 width=28) (actual time=0.031..0.034 rows=0 loops=15,275)

  • Output: (rt.work_status = ANY ('{3,6,14}'::integer[])), rt.prolongation_date, rt.outer_limit, rt.inner_limit
  • Buffers: shared hit=99831
27. 91.650 198.575 ↑ 2.0 1 15,275

Bitmap Heap Scan on public.t_resolution r (cost=4.30..11.94 rows=2 width=32) (actual time=0.012..0.013 rows=1 loops=15,275)

  • 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_enterprise_id, r.signer_employee_id, r.sender_id (...)
  • Recheck Cond: (r.parent_id = d.id)
  • Filter: ((NOT r.del_rec) AND r.controlling)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=16566
  • Buffers: shared hit=47246
28. 106.925 106.925 ↑ 2.0 1 15,275

Bitmap Index Scan on t_resolution_parent_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=15,275)

  • Index Cond: (r.parent_id = d.id)
  • Buffers: shared hit=30680
29. 139.839 139.856 ↓ 0.0 0 17,482

Index Scan using t_resolution_task_parent_id_idx on public.t_resolution_task rt (cost=0.29..28.12 rows=1 width=60) (actual time=0.007..0.008 rows=0 loops=17,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_date, rt.work_s (...)
  • Index Cond: (rt.parent_id = r.id)
  • Filter: ((NOT rt.del_rec) AND (COALESCE(rt.prolongation_date, rt.outer_limit, rt.inner_limit) IS NOT NULL) AND (rt.work_status <> ALL ('{7,13}'::integer[])) AND ((r.signer_enterprise_id = '3176cd78-0acf-467e-aa51-6cd (...)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=52585
30.          

SubPlan (forIndex Scan)

31. 0.007 0.017 ↓ 0.0 0 1

Bitmap Heap Scan on public.t_resolution_task_executer rte (cost=4.45..19.81 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)

  • Recheck Cond: (rte.parent_id = rt.id)
  • Filter: ((NOT rte.del_rec) AND (rte.work_status <> ALL ('{7,13}'::integer[])) AND (rte.enterprise_id = '3176cd78-0acf-467e-aa51-6cdc7960d0a6'::uuid))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1
  • Buffers: shared hit=4
32. 0.010 0.010 ↑ 2.0 2 1

Bitmap Index Scan on t_resolution_task_executer_parent_id_idx (cost=0.00..4.45 rows=4 width=0) (actual time=0.010..0.010 rows=2 loops=1)

  • Index Cond: (rte.parent_id = rt.id)
  • Buffers: shared hit=3
33. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.t_resolution_task_executer rte_1 (cost=0.00..2,159.32 rows=15,513 width=16) (never executed)

  • Output: rte_1.parent_id
  • Filter: ((NOT rte_1.del_rec) AND (rte_1.work_status <> ALL ('{7,13}'::integer[])) AND (rte_1.enterprise_id = '3176cd78-0acf-467e-aa51-6cdc7960d0a6'::uuid))
34. 152.750 519.350 ↑ 1.0 1 15,275

Aggregate (cost=68.23..68.24 rows=1 width=28) (actual time=0.033..0.034 rows=1 loops=15,275)

  • Output: array_agg(DISTINCT CASE WHEN (rt_1.work_status = ANY ('{3,6,14}'::integer[])) THEN 3 ELSE CASE WHEN (COALESCE(rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit) < now()) THEN 5 ELSE 1 END END)
  • Buffers: shared hit=99831
35. 141.715 366.600 ↓ 0.0 0 15,275

Nested Loop (cost=4.59..68.20 rows=2 width=28) (actual time=0.022..0.024 rows=0 loops=15,275)

  • Output: rt_1.work_status, rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit
  • Buffers: shared hit=99831
36. 61.100 137.475 ↑ 2.0 1 15,275

Bitmap Heap Scan on public.t_resolution r_1 (cost=4.30..11.94 rows=2 width=32) (actual time=0.008..0.009 rows=1 loops=15,275)

  • Output: r_1.id, r_1.parent_id, r_1.bo_type, r_1.rec_date, r_1.del_rec, r_1.parent_executer_id, r_1.parent_resolution_id, r_1.controlling, r_1.created_by, r_1.signer_workplace_id, r_1.signer_enterprise_id, r_1.signer_employee_id, r_1.s (...)
  • Recheck Cond: (r_1.parent_id = d.id)
  • Filter: ((NOT r_1.del_rec) AND r_1.controlling)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=16566
  • Buffers: shared hit=47246
37. 76.375 76.375 ↑ 2.0 1 15,275

Bitmap Index Scan on t_resolution_parent_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.005..0.005 rows=1 loops=15,275)

  • Index Cond: (r_1.parent_id = d.id)
  • Buffers: shared hit=30680
38. 87.402 87.410 ↓ 0.0 0 17,482

Index Scan using t_resolution_task_parent_id_idx on public.t_resolution_task rt_1 (cost=0.29..28.12 rows=1 width=60) (actual time=0.005..0.005 rows=0 loops=17,482)

  • 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.period_end_date, r (...)
  • Index Cond: (rt_1.parent_id = r_1.id)
  • Filter: ((NOT rt_1.del_rec) AND (COALESCE(rt_1.prolongation_date, rt_1.outer_limit, rt_1.inner_limit) IS NOT NULL) AND (rt_1.work_status <> ALL ('{7,13}'::integer[])) AND ((r_1.signer_enterprise_id = '3176cd78-0acf-467e-aa51-6cdc7960d (...)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=52585
39.          

SubPlan (forIndex Scan)

40. 0.004 0.008 ↓ 0.0 0 1

Bitmap Heap Scan on public.t_resolution_task_executer rte_2 (cost=4.45..19.81 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Recheck Cond: (rte_2.parent_id = rt_1.id)
  • Filter: ((NOT rte_2.del_rec) AND (rte_2.work_status <> ALL ('{7,13}'::integer[])) AND (rte_2.enterprise_id = '3176cd78-0acf-467e-aa51-6cdc7960d0a6'::uuid))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1
  • Buffers: shared hit=4
41. 0.004 0.004 ↑ 2.0 2 1

Bitmap Index Scan on t_resolution_task_executer_parent_id_idx (cost=0.00..4.45 rows=4 width=0) (actual time=0.004..0.004 rows=2 loops=1)

  • Index Cond: (rte_2.parent_id = rt_1.id)
  • Buffers: shared hit=3
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.t_resolution_task_executer rte_3 (cost=0.00..2,159.32 rows=15,513 width=16) (never executed)

  • Output: rte_3.parent_id
  • Filter: ((NOT rte_3.del_rec) AND (rte_3.work_status <> ALL ('{7,13}'::integer[])) AND (rte_3.enterprise_id = '3176cd78-0acf-467e-aa51-6cdc7960d0a6'::uuid))