explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWJy

Settings
# exclusive inclusive rows x rows loops node
1. 9.400 1,953.699 ↓ 341.0 341 1

Group (cost=17,509.95..17,518.27 rows=1 width=300) (actual time=133.932..1,953.699 rows=341 loops=1)

  • Group Key: cdno.id, tz.id, r.name, ets.description, c.name, c.document_title
2.          

CTE domain_user_with_flowdown_rgs

3. 0.748 9.477 ↓ 10.7 728 1

HashAggregate (cost=14,858.06..14,858.74 rows=68 width=24) (actual time=9.083..9.477 rows=728 loops=1)

  • Group Key: du_1.entity_id, du_1.role_group_id, du_1.user_group_id, du_1.user_id, rg.client_id, rg.entity_type_id
4. 0.206 8.729 ↓ 10.7 728 1

Append (cost=75.23..14,857.04 rows=68 width=24) (actual time=0.664..8.729 rows=728 loops=1)

5. 0.745 4.914 ↓ 11.0 727 1

Nested Loop (cost=75.23..13,079.85 rows=66 width=24) (actual time=0.664..4.914 rows=727 loops=1)

6. 0.248 2.665 ↑ 2.9 752 1

Nested Loop (cost=74.82..12,096.96 rows=2,168 width=24) (actual time=0.651..2.665 rows=752 loops=1)

7. 0.131 0.131 ↓ 2.0 2 1

Index Scan using pk_role_group on role_group rg (cost=0.14..17.37 rows=1 width=12) (actual time=0.019..0.131 rows=2 loops=1)

  • Filter: ((entity_type_id = 13) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
8. 1.812 2.286 ↑ 9.7 376 2

Bitmap Heap Scan on domain_user du_1 (cost=74.68..12,043.00 rows=3,659 width=16) (actual time=0.316..1.143 rows=376 loops=2)

  • Recheck Cond: (role_group_id = rg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 1)))
  • Rows Removed by Filter: 1,055
  • Heap Blocks: exact=273
9. 0.200 0.200 ↑ 4.9 1,435 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.77 rows=6,992 width=0) (actual time=0.100..0.100 rows=1,435 loops=2)

  • Index Cond: (role_group_id = rg.id)
10.          

SubPlan (for Bitmap Heap Scan)

11. 0.000 0.274 ↓ 0.0 0 274

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.001..0.001 rows=0 loops=274)

12. 0.274 0.274 ↓ 0.0 0 274

Seq Scan on user_group ug (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=274)

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 8
13. 1.504 1.504 ↑ 1.0 1 752

Index Scan using cdno_deleted_ix on child_dno cdno_1 (cost=0.41..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=752)

  • Index Cond: (id = du_1.entity_id)
14. 0.000 0.053 ↓ 0.0 0 1

Nested Loop (cost=5.19..969.75 rows=1 width=24) (actual time=0.053..0.053 rows=0 loops=1)

15. 0.003 0.053 ↓ 0.0 0 1

Merge Join (cost=4.78..873.08 rows=17 width=24) (actual time=0.052..0.053 rows=0 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
16. 0.001 0.017 ↑ 34,695.0 1 1

Nested Loop (cost=0.57..118,504.18 rows=34,695 width=20) (actual time=0.017..0.017 rows=1 loops=1)

17. 0.007 0.007 ↑ 16.0 1 1

Index Scan using pk_role_group on role_group prg (cost=0.14..16.83 rows=16 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (entity_type_id = 61)
  • Rows Removed by Filter: 1
18. 0.009 0.009 ↑ 3,659.0 1 1

Index Scan using idx_domain_user_3 on domain_user du_2 (cost=0.42..7,368.87 rows=3,659 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (role_group_id = prg.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 2)))
19.          

SubPlan (for Index Scan)

20. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..1.61 rows=100 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_group ug_1 (cost=0.00..1.10 rows=1 width=32) (never executed)

  • Filter: (id = du_2.user_group_id)
22. 0.002 0.033 ↓ 2.0 2 1

Materialize (cost=4.21..4.22 rows=1 width=16) (actual time=0.032..0.033 rows=2 loops=1)

23. 0.008 0.031 ↓ 2.0 2 1

Sort (cost=4.21..4.22 rows=1 width=16) (actual time=0.031..0.031 rows=2 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
24. 0.004 0.023 ↓ 2.0 2 1

Bitmap Heap Scan on role_group crg (cost=3.09..4.20 rows=1 width=16) (actual time=0.022..0.023 rows=2 loops=1)

  • Recheck Cond: ((entity_type_id = 13) AND (client_id = 1,002))
  • Heap Blocks: exact=2
25. 0.001 0.019 ↓ 0.0 0 1

BitmapAnd (cost=3.09..3.09 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=1)

26. 0.007 0.007 ↑ 1.0 9 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.31 rows=9 width=0) (actual time=0.006..0.007 rows=9 loops=1)

  • Index Cond: (entity_type_id = 13)
27. 0.011 0.011 ↓ 1.8 66 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.52 rows=37 width=0) (actual time=0.011..0.011 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_child_dno_4 on child_dno et (cost=0.41..4.36 rows=133 width=12) (never executed)

  • Index Cond: (contract_id = du_2.entity_id)
  • Filter: (NOT deleted)
29. 0.057 3.556 ↑ 1.0 1 1

Nested Loop (cost=5.19..806.43 rows=1 width=24) (actual time=0.060..3.556 rows=1 loops=1)

30. 0.039 3.428 ↓ 5.5 71 1

Merge Join (cost=4.78..674.72 rows=13 width=24) (actual time=0.043..3.428 rows=71 loops=1)

  • Merge Cond: (prg_1.id = crg_1.flowdown_rolegroup)
31. 0.027 3.354 ↑ 366.5 71 1

Nested Loop (cost=0.57..91,674.83 rows=26,021 width=20) (actual time=0.018..3.354 rows=71 loops=1)

32. 0.064 0.064 ↓ 1.1 13 1

Index Scan using pk_role_group on role_group prg_1 (cost=0.14..16.83 rows=12 width=4) (actual time=0.004..0.064 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
33. 2.930 3.263 ↑ 731.8 5 13

Index Scan using idx_domain_user_3 on domain_user du_3 (cost=0.42..7,601.58 rows=3,659 width=16) (actual time=0.051..0.251 rows=5 loops=13)

  • Index Cond: (role_group_id = prg_1.id)
  • Filter: ((NOT entity_deleted) AND ((user_id = 1,044) OR (SubPlan 3)))
  • Rows Removed by Filter: 420
34.          

SubPlan (for Index Scan)

35. 0.000 0.333 ↓ 0.0 0 333

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.001..0.001 rows=0 loops=333)

36. 0.333 0.333 ↓ 0.0 0 333

Seq Scan on user_group ug_2 (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=333)

  • Filter: (id = du_3.user_group_id)
  • Rows Removed by Filter: 8
37. 0.013 0.035 ↓ 72.0 72 1

Materialize (cost=4.21..4.22 rows=1 width=16) (actual time=0.023..0.035 rows=72 loops=1)

38. 0.004 0.022 ↓ 2.0 2 1

Sort (cost=4.21..4.22 rows=1 width=16) (actual time=0.022..0.022 rows=2 loops=1)

  • Sort Key: crg_1.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
39. 0.003 0.018 ↓ 2.0 2 1

Bitmap Heap Scan on role_group crg_1 (cost=3.09..4.20 rows=1 width=16) (actual time=0.017..0.018 rows=2 loops=1)

  • Recheck Cond: ((entity_type_id = 13) AND (client_id = 1,002))
  • Heap Blocks: exact=2
40. 0.002 0.015 ↓ 0.0 0 1

BitmapAnd (cost=3.09..3.09 rows=1 width=0) (actual time=0.014..0.015 rows=0 loops=1)

41. 0.005 0.005 ↑ 1.0 9 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.31 rows=9 width=0) (actual time=0.005..0.005 rows=9 loops=1)

  • Index Cond: (entity_type_id = 13)
42. 0.008 0.008 ↓ 1.8 66 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.52 rows=37 width=0) (actual time=0.008..0.008 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
43. 0.071 0.071 ↓ 0.0 0 71

Index Scan using idx_child_dno_3 on child_dno et_1 (cost=0.41..7.68 rows=245 width=12) (actual time=0.001..0.001 rows=0 loops=71)

  • Index Cond: (relation_id = du_3.entity_id)
  • Filter: (NOT deleted)
44. 0.458 127.451 ↓ 341.0 341 1

Sort (cost=2,651.21..2,651.21 rows=1 width=450) (actual time=127.250..127.451 rows=341 loops=1)

  • Sort Key: cdno.id, tz.id, r.name, ets.description, c.name, c.document_title
  • Sort Method: quicksort Memory: 115kB
45. 0.146 126.993 ↓ 341.0 341 1

Nested Loop (cost=422.69..2,651.20 rows=1 width=450) (actual time=23.267..126.993 rows=341 loops=1)

46. 0.169 126.165 ↓ 341.0 341 1

Nested Loop Left Join (cost=422.41..2,650.84 rows=1 width=432) (actual time=23.257..126.165 rows=341 loops=1)

47. 0.159 124.632 ↓ 341.0 341 1

Nested Loop Left Join (cost=421.99..2,649.80 rows=1 width=436) (actual time=23.242..124.632 rows=341 loops=1)

48. 0.317 123.791 ↓ 341.0 341 1

Nested Loop Left Join (cost=421.70..2,648.23 rows=1 width=390) (actual time=23.233..123.791 rows=341 loops=1)

49. 56.727 122.792 ↓ 341.0 341 1

Nested Loop (cost=421.42..2,647.56 rows=1 width=343) (actual time=23.222..122.792 rows=341 loops=1)

  • Join Filter: (((du.entity_id = cdno.id) AND (dno.status_id = ANY ('{1511,5,1502,1,1501,1508,1499,1497,1512,1504,6,1500,1510,1509,1496,2,1513,1514,1498,1506,1503,1507,1505,1618,1616,1614,1612,1615,1611,1613,1617}'::integer[]))) OR ((du.entity_id = dno.id) AND (du.role_group_id = 2,002)))
  • Rows Removed by Join Filter: 247,907
50. 10.009 10.009 ↓ 21.4 728 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..64.85 rows=34 width=8) (actual time=9.085..10.009 rows=728 loops=1)

  • Filter: ((user_id = 1,044) OR (SubPlan 6))
51.          

SubPlan (for CTE Scan)

52. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..1.61 rows=100 width=4) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_group ug_4 (cost=0.00..1.10 rows=1 width=32) (never executed)

  • Filter: (id = du.user_group_id)
54. 34.696 56.056 ↓ 2.3 341 728

Materialize (cost=421.42..2,281.46 rows=151 width=347) (actual time=0.018..0.077 rows=341 loops=728)

55. 0.391 21.360 ↓ 2.3 341 1

Nested Loop (cost=421.42..2,280.71 rows=151 width=347) (actual time=12.765..21.360 rows=341 loops=1)

56. 6.347 20.287 ↓ 2.2 341 1

Hash Left Join (cost=421.13..2,131.47 rows=158 width=343) (actual time=12.751..20.287 rows=341 loops=1)

  • Hash Cond: (cdno.time_zone_id = tz.id)
  • Filter: (((change_tz(cdno.due_date, 'UTC'::character varying, tz.time_zone))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(cdno.due_date, 'UTC'::character varying, tz.time_zone))::date <= '2020-09-15 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 573
57. 9.364 13.916 ↑ 1.6 914 1

Bitmap Heap Scan on child_dno cdno (cost=419.21..2,107.58 rows=1,419 width=69) (actual time=5.342..13.916 rows=914 loops=1)

  • Recheck Cond: ((client_id = 1,002) AND (NOT deleted))
  • Filter: (status_id = ANY ('{1628,1626,1621,1620,1622,1627,1623,1624,1625,1619,1568,1,1566,2,4,1567,1563,1564,1565,1562,21}'::integer[]))
  • Rows Removed by Filter: 37
  • Heap Blocks: exact=4,972
58. 4.552 4.552 ↓ 13.7 22,648 1

Bitmap Index Scan on cdno_deleted_ix (cost=0.00..418.86 rows=1,650 width=0) (actual time=4.551..4.552 rows=22,648 loops=1)

  • Index Cond: (client_id = 1,002)
59. 0.011 0.024 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=278) (actual time=0.023..0.024 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
60. 0.013 0.013 ↑ 1.0 41 1

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=278) (actual time=0.004..0.013 rows=41 loops=1)

61. 0.682 0.682 ↑ 1.0 1 341

Index Scan using pk_dno on dno (cost=0.29..0.94 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=341)

  • Index Cond: (id = cdno.dnoid)
  • Filter: (client_id = 1,002)
62. 0.682 0.682 ↑ 1.0 1 341

Index Scan using pk_relation on relation r (cost=0.28..0.67 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=341)

  • Index Cond: (id = cdno.relation_id)
63. 0.682 0.682 ↑ 1.0 1 341

Index Scan using pk_contract on contract c (cost=0.29..1.56 rows=1 width=54) (actual time=0.002..0.002 rows=1 loops=341)

  • Index Cond: (id = cdno.contract_id)
64. 1.364 1.364 ↑ 1.0 1 341

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..1.04 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=341)

  • Index Cond: ((entity_id = dno.id) AND (entity_type_id = 12))
  • Heap Fetches: 360
65. 0.682 0.682 ↑ 1.0 1 341

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..0.35 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=341)

  • Index Cond: (id = cdno.status_id)
66.          

SubPlan (for Group)

67. 1.364 1,816.848 ↑ 1.0 1 341

Aggregate (cost=8.03..8.04 rows=1 width=32) (actual time=5.328..5.328 rows=1 loops=341)

68. 1.364 1,815.484 ↑ 1.0 1 341

Nested Loop Left Join (cost=1.53..8.02 rows=1 width=291) (actual time=5.297..5.324 rows=1 loops=341)

  • Join Filter: (du_4.user_group_id = ug_3.id)
  • Rows Removed by Join Filter: 8
69. 1.364 1,813.097 ↑ 1.0 1 341

Nested Loop Left Join (cost=1.53..6.84 rows=1 width=21) (actual time=5.290..5.317 rows=1 loops=341)

70. 0.682 19.778 ↑ 1.0 1 341

Nested Loop (cost=0.14..4.34 rows=1 width=8) (actual time=0.032..0.058 rows=1 loops=341)

71. 17.732 17.732 ↑ 1.0 1 341

CTE Scan on domain_user_with_flowdown_rgs du_4 (cost=0.00..1.53 rows=1 width=12) (actual time=0.025..0.052 rows=1 loops=341)

  • Filter: (entity_id = cdno.id)
  • Rows Removed by Filter: 727
72. 1.364 1.364 ↑ 1.0 1 341

Index Scan using pk_role_group on role_group rg_1 (cost=0.14..2.37 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=341)

  • Index Cond: (id = du_4.role_group_id)
  • Filter: (entity_type_id = 13)
73. 1,592.811 1,791.955 ↑ 1.0 1 341

Bitmap Heap Scan on app_user u (cost=1.38..2.50 rows=1 width=17) (actual time=5.255..5.255 rows=1 loops=341)

  • Recheck Cond: (du_4.user_id = id)
  • Heap Blocks: exact=1,755,468
74. 199.144 199.144 ↓ 5,152.0 5,152 341

Bitmap Index Scan on pk_app_user (cost=0.00..1.38 rows=1 width=0) (actual time=0.584..0.584 rows=5,152 loops=341)

  • Index Cond: (du_4.user_id = id)
75. 1.023 1.023 ↑ 1.0 8 341

Seq Scan on user_group ug_3 (cost=0.00..1.08 rows=8 width=278) (actual time=0.001..0.003 rows=8 loops=341)

Planning time : 6.127 ms
Execution time : 1,954.285 ms