explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tg4P

Settings
# exclusive inclusive rows x rows loops node
1. 0.104 55.154 ↑ 33.3 6 1

HashAggregate (cost=18,018,607.18..18,018,612.18 rows=200 width=124) (actual time=55.144..55.154 rows=6 loops=1)

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

CTE dep_docs

3. 0.039 55.008 ↑ 3.3 123 1

Unique (cost=18,018,589.71..18,018,591.76 rows=411 width=94) (actual time=54.951..55.008 rows=123 loops=1)

4. 0.147 54.969 ↑ 3.0 137 1

Sort (cost=18,018,589.71..18,018,590.74 rows=411 width=94) (actual time=54.951..54.969 rows=137 loops=1)

  • Sort Key: dc.document_id, (CASE WHEN ((dc.stop_date IS NULL) AND (dc.exec_date < ('now'::cstring)::date)) THEN 1 ELSE 0 END) DESC
  • Sort Method: quicksort Memory: 45kB
5. 0.296 54.822 ↑ 3.0 137 1

Nested Loop (cost=39,958.27..18,018,571.87 rows=411 width=94) (actual time=1.874..54.822 rows=137 loops=1)

6. 0.176 51.512 ↑ 3.0 137 1

Nested Loop (cost=809.15..1,928,270.44 rows=411 width=96) (actual time=1.829..51.512 rows=137 loops=1)

7. 0.041 49.664 ↑ 2.7 152 1

Nested Loop (cost=2.55..1,596,749.62 rows=411 width=73) (actual time=1.791..49.664 rows=152 loops=1)

8. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: (id = 7)
  • Heap Fetches: 0
9. 0.011 49.608 ↑ 2.7 152 1

Nested Loop (cost=2.13..1,596,741.08 rows=411 width=40) (actual time=1.776..49.608 rows=152 loops=1)

10. 0.042 42.325 ↑ 5.7 808 1

Nested Loop (cost=1.56..1,574,795.42 rows=4,611 width=48) (actual time=0.127..42.325 rows=808 loops=1)

11. 2.210 25.435 ↑ 11.0 1,872 1

Nested Loop (cost=0.99..1,491,224.41 rows=20,671 width=48) (actual time=0.038..25.435 rows=1,872 loops=1)

12. 6.149 6.149 ↑ 1.0 2,846 1

Index Scan using "idx$$_6d740003" on usr ue (cost=0.42..11,682.14 rows=2,982 width=24) (actual time=0.019..6.149 rows=2,846 loops=1)

  • Index Cond: (group_id = 7)
13. 17.076 17.076 ↑ 160.0 1 2,846

Index Scan using dc_executor_idx on document_control dc (cost=0.57..494.56 rows=160 width=40) (actual time=0.005..0.006 rows=1 loops=2,846)

  • Index Cond: ((executor = ue.id) AND (is_control = 1))
14. 16.848 16.848 ↓ 0.0 0 1,872

Index Scan using document_n_uniq_idx on document_n dn (cost=0.57..4.03 rows=1 width=16) (actual time=0.008..0.009 rows=0 loops=1,872)

  • Index Cond: ((document_id = dc.document_id) AND (org_id = 7))
  • Filter: ((rdate >= '2018-10-03 00:00:00'::timestamp without time zone) AND (rdate <= '2019-10-03 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
15. 7.272 7.272 ↓ 0.0 0 808

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

  • Index Cond: (id = dc.resolution_to_id)
  • Filter: (another_control = 1)
  • Rows Removed by Filter: 1
16. 0.000 1.672 ↑ 1.0 1 152

Limit (cost=806.60..806.60 rows=1 width=76) (actual time=0.011..0.011 rows=1 loops=152)

17.          

CTE x

18. 0.113 1.064 ↑ 101.0 1 152

Recursive Union (cost=0.29..804.07 rows=101 width=107) (actual time=0.003..0.007 rows=1 loops=152)

19. 0.456 0.456 ↑ 1.0 1 152

Index Scan using org_id_parentid_idx on org o1 (cost=0.29..8.30 rows=1 width=107) (actual time=0.002..0.003 rows=1 loops=152)

  • Index Cond: (id = $1)
20. 0.195 0.495 ↓ 0.0 0 165

Nested Loop (cost=0.29..79.38 rows=10 width=107) (actual time=0.003..0.003 rows=0 loops=165)

21. 0.000 0.000 ↑ 10.0 1 165

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

22. 0.300 0.300 ↓ 0.0 0 150

Index Scan using org_id_parentid_idx on org o2 (cost=0.29..7.91 rows=1 width=103) (actual time=0.002..0.002 rows=0 loops=150)

  • Index Cond: (id = x.parent_id)
  • Filter: (name <> $2)
  • Rows Removed by Filter: 1
23. 0.456 1.672 ↑ 101.0 1 152

Sort (cost=2.53..2.78 rows=101 width=76) (actual time=0.010..0.011 rows=1 loops=152)

  • Sort Key: x_2.lvl DESC
  • Sort Method: top-N heapsort Memory: 25kB
24. 1.216 1.216 ↑ 101.0 1 152

CTE Scan on x x_2 (cost=0.00..2.02 rows=101 width=76) (actual time=0.004..0.008 rows=1 loops=152)

25. 0.137 3.014 ↑ 1.0 1 137

Limit (cost=39,149.12..39,149.12 rows=1 width=30) (actual time=0.022..0.022 rows=1 loops=137)

26.          

CTE x

27. 0.194 1.096 ↑ 731.0 1 137

Recursive Union (cost=0.29..487.00 rows=731 width=67) (actual time=0.003..0.008 rows=1 loops=137)

28. 0.274 0.274 ↑ 1.0 1 137

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=137)

  • Index Cond: (id = $6)
29. 0.082 0.628 ↓ 0.0 0 157

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

30. 0.000 0.000 ↑ 10.0 1 157

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

31. 0.546 0.546 ↓ 0.0 0 182

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.003..0.003 rows=0 loops=182)

  • Index Cond: (parent_id = x_1.id)
  • Heap Fetches: 0
32. 0.411 2.877 ↑ 9,425.0 1 137

Sort (cost=38,662.12..38,685.69 rows=9,425 width=30) (actual time=0.021..0.021 rows=1 loops=137)

  • Sort Key: usr.vip_type DESC
  • Sort Method: top-N heapsort Memory: 25kB
33. 0.141 2.466 ↑ 3,141.7 3 137

Nested Loop (cost=0.42..38,615.00 rows=9,425 width=30) (actual time=0.008..0.018 rows=3 loops=137)

34. 1.233 1.233 ↑ 731.0 1 137

CTE Scan on x x_3 (cost=0.00..14.62 rows=731 width=8) (actual time=0.003..0.009 rows=1 loops=137)

35. 1.092 1.092 ↑ 6.5 2 182

Index Scan using usr_orgid_id_idx on usr (cost=0.42..52.67 rows=13 width=30) (actual time=0.003..0.006 rows=2 loops=182)

  • Index Cond: (org_id = x_3.id)
36. 55.050 55.050 ↑ 3.3 123 1

CTE Scan on dep_docs (cost=0.00..8.22 rows=411 width=104) (actual time=54.954..55.050 rows=123 loops=1)

Planning time : 9.221 ms
Execution time : 55.454 ms