explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tfcQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.356 4,304.763 ↑ 33.3 6 1

HashAggregate (cost=2,026,580.86..2,026,585.86 rows=200 width=132) (actual time=4,304.747..4,304.763 rows=6 loops=1)

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

CTE dep_docs

3. 0.476 4,304.275 ↑ 3.2 129 1

Nested Loop (cost=868.14..2,026,564.34 rows=413 width=94) (actual time=105.933..4,304.275 rows=129 loops=1)

4. 0.240 4,295.546 ↑ 3.2 131 1

Nested Loop (cost=811.76..2,003,266.90 rows=413 width=88) (actual time=101.302..4,295.546 rows=131 loops=1)

5. 0.073 4,282.796 ↑ 3.0 139 1

Nested Loop (cost=2.55..1,667,809.76 rows=413 width=24) (actual time=99.063..4,282.796 rows=139 loops=1)

6. 0.044 0.044 ↑ 1.0 1 1

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

  • Index Cond: (id = 7)
  • Heap Fetches: 0
7. 0.491 4,282.679 ↑ 3.0 139 1

Nested Loop (cost=2.13..1,667,797.19 rows=413 width=32) (actual time=99.043..4,282.679 rows=139 loops=1)

8. 1.784 4,071.438 ↑ 6.2 750 1

Nested Loop (cost=1.56..1,645,770.38 rows=4,644 width=40) (actual time=8.421..4,071.438 rows=750 loops=1)

9. 3.526 3,836.878 ↑ 11.4 1,908 1

Nested Loop (cost=0.99..1,557,997.59 rows=21,764 width=48) (actual time=6.759..3,836.878 rows=1,908 loops=1)

10. 2,167.857 2,167.857 ↑ 1.1 2,847 1

Index Scan using "idx$$_6d740003" on usr ue (cost=0.42..12,195.28 rows=3,111 width=24) (actual time=6.433..2,167.857 rows=2,847 loops=1)

  • Index Cond: (group_id = 7)
11. 1,665.495 1,665.495 ↑ 161.0 1 2,847

Index Scan using dc_executor_idx on document_control dc (cost=0.57..495.27 rows=161 width=40) (actual time=0.579..0.585 rows=1 loops=2,847)

  • Index Cond: ((executor = ue.id) AND (is_control = 1))
12. 232.776 232.776 ↓ 0.0 0 1,908

Index Scan using document_n_uniq_idx on document_n dn (cost=0.57..4.02 rows=1 width=16) (actual time=0.120..0.122 rows=0 loops=1,908)

  • Index Cond: ((document_id = dc.document_id) AND (org_id = 7))
  • 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
13. 210.750 210.750 ↓ 0.0 0 750

Index Scan using sys_c0014909 on resolution_to rt (cost=0.57..4.73 rows=1 width=8) (actual time=0.280..0.281 rows=0 loops=750)

  • Index Cond: (id = dc.resolution_to_id)
  • Filter: (another_control = 1)
  • Rows Removed by Filter: 1
14. 0.139 12.510 ↑ 1.0 1 139

Subquery Scan on t (cost=809.20..812.23 rows=1 width=72) (actual time=0.088..0.090 rows=1 loops=139)

  • Filter: (t.lead_parent_id = 0)
  • Rows Removed by Filter: 2
15. 0.834 12.371 ↑ 33.7 3 139

WindowAgg (cost=809.20..810.97 rows=101 width=84) (actual time=0.087..0.089 rows=3 loops=139)

16.          

CTE t_prior

17. 0.731 10.286 ↑ 33.7 3 139

Recursive Union (cost=0.29..803.82 rows=101 width=138) (actual time=0.061..0.074 rows=3 loops=139)

18. 8.340 8.340 ↑ 1.0 1 139

Index Scan using org_id_parentid_idx on org o (cost=0.29..8.30 rows=1 width=138) (actual time=0.060..0.060 rows=1 loops=139)

  • Index Cond: (id = $2)
19. 0.421 1.215 ↑ 10.0 1 405

Nested Loop (cost=0.29..79.35 rows=10 width=138) (actual time=0.003..0.003 rows=1 loops=405)

20. 0.000 0.000 ↑ 10.0 1 405

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

21. 0.794 0.794 ↑ 1.0 1 397

Index Scan using org_id_parentid_idx on org o_1 (cost=0.29..7.90 rows=1 width=134) (actual time=0.002..0.002 rows=1 loops=397)

  • Index Cond: (id = t_prior.parent_id)
22. 0.695 11.537 ↑ 33.7 3 139

Sort (cost=5.38..5.63 rows=101 width=84) (actual time=0.083..0.083 rows=3 loops=139)

  • Sort Key: t_prior_1.level
  • Sort Method: quicksort Memory: 25kB
23. 10.842 10.842 ↑ 33.7 3 139

CTE Scan on t_prior t_prior_1 (cost=0.00..2.02 rows=101 width=84) (actual time=0.063..0.078 rows=3 loops=139)

24. 0.131 8.253 ↑ 1.0 1 131

Limit (cost=56.38..56.38 rows=1 width=22) (actual time=0.063..0.063 rows=1 loops=131)

25. 0.393 8.122 ↑ 13.0 1 131

Sort (cost=56.38..56.41 rows=13 width=22) (actual time=0.062..0.062 rows=1 loops=131)

  • Sort Key: u.vip_type DESC
  • Sort Method: quicksort Memory: 25kB
26. 7.729 7.729 ↑ 13.0 1 131

Index Scan using usr_orgid_id_idx on usr u (cost=0.42..56.32 rows=13 width=22) (actual time=0.058..0.059 rows=1 loops=131)

  • Index Cond: (org_id = t.id)
27. 4,304.407 4,304.407 ↑ 3.2 129 1

CTE Scan on dep_docs (cost=0.00..8.26 rows=413 width=112) (actual time=105.936..4,304.407 rows=129 loops=1)

Planning time : 9.308 ms
Execution time : 4,305.124 ms