explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zXG1

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 16.791 ↓ 2.0 4 1

HashAggregate (cost=266,998.30..266,998.35 rows=2 width=1,136) (actual time=16.788..16.791 rows=4 loops=1)

  • Group Key: dep_docs.id, dep_docs.dep_idx, dep_docs.dep_name, dep_docs.name
2.          

CTE dep_docs

3. 0.013 16.760 ↓ 4.5 9 1

Sort (cost=266,998.22..266,998.22 rows=2 width=622) (actual time=16.760..16.760 rows=9 loops=1)

  • Sort Key: dc.document_id DESC
  • Sort Method: quicksort Memory: 26kB
4. 0.026 16.747 ↓ 4.5 9 1

Nested Loop (cost=38,033.36..266,998.21 rows=2 width=622) (actual time=0.773..16.747 rows=9 loops=1)

5. 0.020 16.406 ↓ 4.5 9 1

Nested Loop (cost=169.20..191,269.83 rows=2 width=616) (actual time=0.632..16.406 rows=9 loops=1)

6. 0.003 16.086 ↓ 12.5 25 1

Nested Loop (cost=2.56..190,936.50 rows=2 width=32) (actual time=0.546..16.086 rows=25 loops=1)

7. 0.011 0.011 ↑ 1.0 1 1

Index Only Scan using ug_idname_idx on user_group ugd (cost=0.42..4.44 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (id = 2)
  • Heap Fetches: 0
8. 0.021 16.072 ↓ 12.5 25 1

Nested Loop (cost=2.13..190,932.04 rows=2 width=40) (actual time=0.535..16.072 rows=25 loops=1)

9. 0.000 15.736 ↓ 1.5 35 1

Nested Loop (cost=1.56..190,819.21 rows=24 width=48) (actual time=0.522..15.736 rows=35 loops=1)

10. 0.509 6.055 ↑ 1.4 1,621 1

Nested Loop (cost=0.99..181,987.55 rows=2,214 width=48) (actual time=0.030..6.055 rows=1,621 loops=1)

11. 0.836 0.836 ↓ 1.0 314 1

Index Scan using quest_sx_idx611b3981b197b636e7 on usr ue (cost=0.42..1,171.51 rows=309 width=24) (actual time=0.013..0.836 rows=314 loops=1)

  • Index Cond: (group_id = 2)
12. 4.710 4.710 ↑ 33.0 5 314

Index Scan using dc_executor_idx on document_control dc (cost=0.57..583.52 rows=165 width=40) (actual time=0.008..0.015 rows=5 loops=314)

  • Index Cond: ((executor = ue.id) AND (is_control = 1))
13. 9.726 9.726 ↓ 0.0 0 1,621

Index Scan using document_n_uniq_idx on document_n dn (cost=0.57..3.98 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1,621)

  • Index Cond: ((document_id = dc.document_id) AND (org_id = 2))
  • Filter: ((rdate >= '2018-10-01 00:00:00'::timestamp without time zone) AND (rdate <= '2019-10-01 00:00:00'::timestamp without time zone) AND (d_deleted = 0) AND (status = 8))
  • Rows Removed by Filter: 1
14. 0.315 0.315 ↑ 1.0 1 35

Index Scan using sys_c0014909 on resolution_to rt (cost=0.57..4.69 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=35)

  • Index Cond: (id = dc.resolution_to_id)
  • Filter: (another_control = 1)
  • Rows Removed by Filter: 0
15. 0.000 0.300 ↓ 0.0 0 25

Limit (cost=166.64..166.65 rows=1 width=596) (actual time=0.012..0.012 rows=0 loops=25)

16.          

CTE x

17. 0.000 0.225 ↓ 0.0 0 25

Recursive Union (cost=0.29..166.37 rows=11 width=135) (actual time=0.004..0.009 rows=0 loops=25)

18. 0.050 0.050 ↓ 0.0 0 25

Index Scan using org_id_parentid_idx on org o1 (cost=0.29..8.30 rows=1 width=135) (actual time=0.002..0.002 rows=0 loops=25)

  • Index Cond: (id = $1)
19. 0.069 0.175 ↓ 0.0 0 25

Hash Join (cost=0.61..15.78 rows=1 width=135) (actual time=0.007..0.007 rows=0 loops=25)

  • Hash Cond: (o2.id = x.parent_id)
20. 0.081 0.081 ↓ 4.5 9 9

Index Scan using fk_idx_vvcr on org o2 (cost=0.29..15.44 rows=2 width=131) (actual time=0.003..0.009 rows=9 loops=9)

  • Index Cond: (group_id = $1)
  • Filter: (ordi IS NOT NULL)
  • Rows Removed by Filter: 0
21. 0.025 0.025 ↓ 0.0 0 25

Hash (cost=0.20..0.20 rows=10 width=12) (actual time=0.001..0.001 rows=0 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
22. 0.000 0.000 ↓ 0.0 0 25

WorkTable Scan on x (cost=0.00..0.20 rows=10 width=12) (actual time=0.000..0.000 rows=0 loops=25)

23. 0.050 0.300 ↓ 0.0 0 25

Sort (cost=0.28..0.30 rows=11 width=596) (actual time=0.012..0.012 rows=0 loops=25)

  • Sort Key: x_2.lvl DESC
  • Sort Method: quicksort Memory: 25kB
24. 0.250 0.250 ↓ 0.0 0 25

CTE Scan on x x_2 (cost=0.00..0.22 rows=11 width=596) (actual time=0.004..0.010 rows=0 loops=25)

25. 0.000 0.315 ↑ 1.0 1 9

Limit (cost=37,864.16..37,864.16 rows=1 width=30) (actual time=0.035..0.035 rows=1 loops=9)

26.          

CTE x

27. 0.023 0.117 ↑ 243.7 3 9

Recursive Union (cost=0.29..487.00 rows=731 width=67) (actual time=0.002..0.013 rows=3 loops=9)

28. 0.018 0.018 ↑ 1.0 1 9

Index Scan using org_id_parentid_idx on org o1_1 (cost=0.29..8.30 rows=1 width=67) (actual time=0.002..0.002 rows=1 loops=9)

  • Index Cond: (id = $4)
29. 0.001 0.076 ↑ 73.0 1 19

Nested Loop (cost=0.41..46.41 rows=73 width=67) (actual time=0.004..0.004 rows=1 loops=19)

30. 0.000 0.000 ↑ 10.0 1 19

WorkTable Scan on x x_1 (cost=0.00..0.20 rows=10 width=12) (actual time=0.000..0.000 rows=1 loops=19)

31. 0.075 0.075 ↑ 7.0 1 25

Index Only Scan using org_id_par_test3_idx on org o2_1 (cost=0.41..4.53 rows=7 width=63) (actual time=0.002..0.003 rows=1 loops=25)

  • Index Cond: (parent_id = x_1.id)
  • Heap Fetches: 13
32. 0.036 0.315 ↑ 9,692.0 1 9

Sort (cost=37,377.16..37,401.39 rows=9,692 width=30) (actual time=0.035..0.035 rows=1 loops=9)

  • Sort Key: usr.vip_type DESC
  • Sort Method: quicksort Memory: 25kB
33. 0.003 0.279 ↑ 1,615.3 6 9

Nested Loop (cost=0.42..37,328.70 rows=9,692 width=30) (actual time=0.008..0.031 rows=6 loops=9)

34. 0.126 0.126 ↑ 243.7 3 9

CTE Scan on x x_3 (cost=0.00..14.62 rows=731 width=8) (actual time=0.002..0.014 rows=3 loops=9)

35. 0.150 0.150 ↑ 6.5 2 25

Index Scan using usr_orgid_id_idx on usr (cost=0.42..50.92 rows=13 width=30) (actual time=0.004..0.006 rows=2 loops=25)

  • Index Cond: (org_id = x_3.id)
36. 16.763 16.763 ↓ 4.5 9 1

CTE Scan on dep_docs (cost=0.00..0.04 rows=2 width=1,116) (actual time=16.760..16.763 rows=9 loops=1)

Planning time : 4.433 ms
Execution time : 17.045 ms