explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g4RE

Settings
# exclusive inclusive rows x rows loops node
1. 0.077 836.880 ↑ 6.5 50 1

Sort (cost=7,128.41..7,129.22 rows=324 width=2,299) (actual time=836.872..836.880 rows=50 loops=1)

  • Output: security_audit.audit_id, audit_categories.category_lcode, audit_importance.importance_lcode, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, users.user_display_name, user_auth_credentials.auth_login, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, source_types.source_type_lcode, security_audit.source_address, audit_auth_results.auth_result_lcode, audit_action_results.aa_res_lcode, security_audit.audit_desc, security_audit.action_time_start, security_audit.action_time_end, security_audit.diagnostic_message, tab.rn
  • Sort Key: tab.rn
  • Sort Method: quicksort Memory: 38kB
2. 0.028 836.803 ↑ 6.5 50 1

Hash Left Join (cost=6,099.38..7,114.90 rows=324 width=2,299) (actual time=194.780..836.803 rows=50 loops=1)

  • Output: security_audit.audit_id, audit_categories.category_lcode, audit_importance.importance_lcode, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, users.user_display_name, user_auth_credentials.auth_login, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, source_types.source_type_lcode, security_audit.source_address, audit_auth_results.auth_result_lcode, audit_action_results.aa_res_lcode, security_audit.audit_desc, security_audit.action_time_start, security_audit.action_time_end, security_audit.diagnostic_message, tab.rn
  • Hash Cond: (security_audit.user_id = user_auth_credentials.user_id)
3. 0.021 836.770 ↑ 6.5 50 1

Hash Left Join (cost=6,098.33..7,109.40 rows=324 width=2,276) (actual time=194.770..836.770 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, audit_categories.category_lcode, audit_importance.importance_lcode, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode, source_types.source_type_lcode, audit_auth_results.auth_result_lcode, audit_action_results.aa_res_lcode, users.user_display_name
  • Hash Cond: (security_audit.user_id = users.user_id)
4. 0.024 836.741 ↑ 6.5 50 1

Hash Left Join (cost=6,097.22..7,103.83 rows=324 width=2,267) (actual time=194.755..836.741 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, audit_categories.category_lcode, audit_importance.importance_lcode, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode, source_types.source_type_lcode, audit_auth_results.auth_result_lcode, audit_action_results.aa_res_lcode
  • Hash Cond: (security_audit.action_result_id = audit_action_results.aa_res_id)
5. 0.034 836.710 ↑ 6.5 50 1

Hash Join (cost=6,096.15..7,098.31 rows=324 width=2,261) (actual time=194.742..836.710 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, security_audit.action_result_id, audit_categories.category_lcode, audit_importance.importance_lcode, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode, source_types.source_type_lcode, audit_auth_results.auth_result_lcode
  • Hash Cond: (security_audit.auth_result_id = audit_auth_results.auth_result_id)
6. 0.033 836.636 ↑ 6.5 50 1

Hash Join (cost=6,095.11..7,092.81 rows=324 width=2,260) (actual time=194.691..836.636 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, security_audit.auth_result_id, security_audit.action_result_id, audit_categories.category_lcode, audit_importance.importance_lcode, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode, source_types.source_type_lcode
  • Hash Cond: (security_audit.source_type_id = source_types.source_type_id)
7. 0.026 836.593 ↑ 6.5 50 1

Merge Join (cost=6,094.06..7,087.31 rows=324 width=2,261) (actual time=194.667..836.593 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, security_audit.source_type_id, security_audit.auth_result_id, security_audit.action_result_id, audit_categories.category_lcode, audit_importance.importance_lcode, permissions.perm_desc, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode
  • Merge Cond: (security_audit.perm_id = permissions.perm_id)
8. 0.019 836.460 ↑ 6.5 50 1

Nested Loop (cost=6,087.93..326,576.86 rows=324 width=2,235) (actual time=194.554..836.460 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, security_audit.perm_id, security_audit.source_type_id, security_audit.auth_result_id, security_audit.action_result_id, audit_categories.category_lcode, audit_importance.importance_lcode
  • Join Filter: (security_audit.importance_id = audit_importance.importance_id)
  • Rows Removed by Join Filter: 150
9. 0.005 836.391 ↑ 6.5 50 1

Nested Loop (cost=6,087.93..326,556.37 rows=324 width=2,234) (actual time=194.539..836.391 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, security_audit.importance_id, security_audit.perm_id, security_audit.source_type_id, security_audit.auth_result_id, security_audit.action_result_id, audit_categories.category_lcode
  • Join Filter: (security_audit.category_id = audit_categories.category_id)
  • Rows Removed by Join Filter: 50
10. 483.990 836.336 ↑ 6.5 50 1

Nested Loop (cost=6,087.93..326,545.63 rows=324 width=2,222) (actual time=194.522..836.336 rows=50 loops=1)

  • Output: tab.rn, security_audit.audit_id, security_audit.user_session_id, security_audit.created, security_audit.action_time_start, security_audit.action_time_end, security_audit.user_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_address, security_audit.audit_desc, security_audit.diagnostic_message, security_audit.category_id, security_audit.importance_id, security_audit.perm_id, security_audit.source_type_id, security_audit.auth_result_id, security_audit.action_result_id
  • Join Filter: (tab.audit_id = security_audit.audit_id)
  • Rows Removed by Join Filter: 3248600
11. 27.481 27.481 ↓ 1.0 64,973 1

Index Scan using idx_sa_perm_id on ng_security.security_audit (cost=0.29..2,987.74 rows=64,889 width=2,214) (actual time=0.006..27.481 rows=64,973 loops=1)

  • Output: security_audit.audit_id, security_audit.category_id, security_audit.importance_id, security_audit.user_session_id, security_audit.created, security_audit.user_id, security_audit.perm_id, security_audit.object_type_desc, security_audit.object_id, security_audit.object_revision_id, security_audit.object_name, security_audit.source_type_id, security_audit.source_address, security_audit.auth_result_id, security_audit.action_result_id, security_audit.audit_desc, security_audit.action_time_start, security_audit.action_time_end, security_audit.diagnostic_message
12. 131.743 324.865 ↑ 6.5 50 64,973

Materialize (cost=6,087.64..8,198.15 rows=324 width=14) (actual time=0.003..0.005 rows=50 loops=64,973)

  • Output: tab.rn, tab.audit_id
13. 5.682 193.122 ↑ 6.5 50 1

Subquery Scan on tab (cost=6,087.64..8,196.53 rows=324 width=14) (actual time=166.261..193.122 rows=50 loops=1)

  • Output: tab.rn, tab.audit_id
  • Filter: ((tab.rn >= 1) AND (tab.rn <= 50))
  • Rows Removed by Filter: 64923
14. 14.150 187.440 ↓ 1.0 64,973 1

WindowAgg (cost=6,087.64..7,223.20 rows=64,889 width=22) (actual time=166.255..187.440 rows=64,973 loops=1)

  • Output: security_audit_1.audit_id, row_number() OVER (?), security_audit_1.action_time_start
15. 46.265 173.290 ↓ 1.0 64,973 1

Sort (cost=6,087.64..6,249.86 rows=64,889 width=14) (actual time=166.244..173.290 rows=64,973 loops=1)

  • Output: security_audit_1.action_time_start, security_audit_1.audit_id
  • Sort Key: security_audit_1.action_time_start DESC
  • Sort Method: external sort Disk: 1576kB
16. 14.534 127.025 ↓ 1.0 64,973 1

Merge Semi Join (cost=84.34..901.17 rows=64,889 width=14) (actual time=0.135..127.025 rows=64,973 loops=1)

  • Output: security_audit_1.action_time_start, security_audit_1.audit_id
  • Merge Cond: (security_audit_1.perm_id = permissions_1.perm_id)
17. 86.354 112.378 ↓ 1.0 64,973 1

Nested Loop (cost=0.29..7,855.48 rows=64,889 width=19) (actual time=0.019..112.378 rows=64,973 loops=1)

  • Output: security_audit_1.audit_id, security_audit_1.action_time_start, security_audit_1.perm_id
  • Join Filter: (security_audit_1.user_id = users_1.user_id)
  • Rows Removed by Join Filter: 259892
18. 26.024 26.024 ↓ 1.0 64,973 1

Index Scan using idx_sa_perm_id on ng_security.security_audit security_audit_1 (cost=0.29..2,987.74 rows=64,889 width=24) (actual time=0.004..26.024 rows=64,973 loops=1)

  • Output: security_audit_1.audit_id, security_audit_1.category_id, security_audit_1.importance_id, security_audit_1.user_session_id, security_audit_1.created, security_audit_1.user_id, security_audit_1.perm_id, security_audit_1.object_type_desc, security_audit_1.object_id, security_audit_1.object_revision_id, security_audit_1.object_name, security_audit_1.source_type_id, security_audit_1.source_address, security_audit_1.auth_result_id, security_audit_1.action_result_id, security_audit_1.audit_desc, security_audit_1.action_time_start, security_audit_1.action_time_end, security_audit_1.diagnostic_message
19. 0.000 0.000 ↑ 1.0 5 64,973

Materialize (cost=0.00..1.07 rows=5 width=5) (actual time=0.000..0.000 rows=5 loops=64,973)

  • Output: users_1.user_id
20. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on ng_user.users users_1 (cost=0.00..1.05 rows=5 width=5) (actual time=0.003..0.003 rows=5 loops=1)

  • Output: users_1.user_id
21. 0.017 0.113 ↓ 1.0 30 1

Sort (cost=5.70..5.77 rows=29 width=4) (actual time=0.111..0.113 rows=30 loops=1)

  • Output: permissions_1.perm_id
  • Sort Key: permissions_1.perm_id
  • Sort Method: quicksort Memory: 26kB
22. 0.021 0.096 ↓ 1.0 30 1

Hash Join (cost=3.31..4.99 rows=29 width=4) (actual time=0.074..0.096 rows=30 loops=1)

  • Output: permissions_1.perm_id
  • Hash Cond: (permissions_1.perm_action_id = permission_actions_1.perm_action_id)
23. 0.027 0.063 ↓ 1.0 30 1

Hash Join (cost=2.08..3.51 rows=29 width=7) (actual time=0.048..0.063 rows=30 loops=1)

  • Output: permissions_1.perm_action_id, permissions_1.perm_id
  • Hash Cond: (permissions_1.obj_type_id = biz_objects_types_1.obj_type_id)
24. 0.007 0.007 ↓ 1.0 30 1

Seq Scan on ng_security.permissions permissions_1 (cost=0.00..1.29 rows=29 width=11) (actual time=0.007..0.007 rows=30 loops=1)

  • Output: permissions_1.perm_id, permissions_1.obj_type_id, permissions_1.perm_action_id, permissions_1.perm_desc
25. 0.004 0.029 ↓ 5.0 10 1

Hash (cost=2.06..2.06 rows=2 width=4) (actual time=0.029..0.029 rows=10 loops=1)

  • Output: biz_objects_types_1.obj_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.017 0.025 ↓ 5.0 10 1

Nested Loop (cost=0.00..2.06 rows=2 width=4) (actual time=0.009..0.025 rows=10 loops=1)

  • Output: biz_objects_types_1.obj_type_id
  • Join Filter: (biz_objects_types_1.namespace_id = namespaces_1.namespace_id)
  • Rows Removed by Join Filter: 30
27. 0.004 0.004 ↓ 4.0 4 1

Seq Scan on ng_commons.namespaces namespaces_1 (cost=0.00..1.01 rows=1 width=5) (actual time=0.003..0.004 rows=4 loops=1)

  • Output: namespaces_1.namespace_id, namespaces_1.namespace_lcode
28. 0.004 0.004 ↓ 5.0 10 4

Seq Scan on ng_commons.biz_objects_types biz_objects_types_1 (cost=0.00..1.02 rows=2 width=9) (actual time=0.001..0.001 rows=10 loops=4)

  • Output: biz_objects_types_1.obj_type_id, biz_objects_types_1.obj_type_lcode, biz_objects_types_1.namespace_id
29. 0.008 0.012 ↓ 1.1 11 1

Hash (cost=1.10..1.10 rows=10 width=4) (actual time=0.012..0.012 rows=11 loops=1)

  • Output: permission_actions_1.perm_action_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.004 0.004 ↓ 1.1 11 1

Seq Scan on ng_security.permission_actions permission_actions_1 (cost=0.00..1.10 rows=10 width=4) (actual time=0.003..0.004 rows=11 loops=1)

  • Output: permission_actions_1.perm_action_id
31. 0.042 0.050 ↑ 1.0 2 50

Materialize (cost=0.00..1.03 rows=2 width=22) (actual time=0.000..0.001 rows=2 loops=50)

  • Output: audit_categories.category_lcode, audit_categories.category_id
32. 0.008 0.008 ↑ 1.0 2 1

Seq Scan on ng_security.audit_categories (cost=0.00..1.02 rows=2 width=22) (actual time=0.007..0.008 rows=2 loops=1)

  • Output: audit_categories.category_lcode, audit_categories.category_id
33. 0.038 0.050 ↑ 1.0 4 50

Materialize (cost=0.00..1.06 rows=4 width=11) (actual time=0.000..0.001 rows=4 loops=50)

  • Output: audit_importance.importance_lcode, audit_importance.importance_id
34. 0.012 0.012 ↑ 1.0 4 1

Seq Scan on ng_security.audit_importance (cost=0.00..1.04 rows=4 width=11) (actual time=0.011..0.012 rows=4 loops=1)

  • Output: audit_importance.importance_lcode, audit_importance.importance_id
35. 0.021 0.107 ↓ 1.0 30 1

Sort (cost=5.70..5.78 rows=29 width=35) (actual time=0.103..0.107 rows=30 loops=1)

  • Output: permissions.perm_desc, permissions.perm_id, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode
  • Sort Key: permissions.perm_id
  • Sort Method: quicksort Memory: 27kB
36. 0.017 0.086 ↓ 1.0 30 1

Hash Join (cost=3.31..5.00 rows=29 width=35) (actual time=0.060..0.086 rows=30 loops=1)

  • Output: permissions.perm_desc, permissions.perm_id, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode, permission_actions.perm_action_lcode
  • Hash Cond: (permissions.perm_action_id = permission_actions.perm_action_id)
37. 0.019 0.058 ↓ 1.0 30 1

Hash Left Join (cost=2.09..3.52 rows=29 width=31) (actual time=0.040..0.058 rows=30 loops=1)

  • Output: permissions.perm_desc, permissions.perm_id, permissions.perm_action_id, biz_objects_types.obj_type_lcode, namespaces.namespace_lcode
  • Hash Cond: (permissions.obj_type_id = biz_objects_types.obj_type_id)
38. 0.011 0.011 ↓ 1.0 30 1

Seq Scan on ng_security.permissions (cost=0.00..1.29 rows=29 width=22) (actual time=0.005..0.011 rows=30 loops=1)

  • Output: permissions.perm_id, permissions.obj_type_id, permissions.perm_action_id, permissions.perm_desc
39. 0.004 0.028 ↓ 5.0 10 1

Hash (cost=2.06..2.06 rows=2 width=17) (actual time=0.028..0.028 rows=10 loops=1)

  • Output: biz_objects_types.obj_type_lcode, biz_objects_types.obj_type_id, namespaces.namespace_lcode
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.009 0.024 ↓ 5.0 10 1

Nested Loop Left Join (cost=0.00..2.06 rows=2 width=17) (actual time=0.013..0.024 rows=10 loops=1)

  • Output: biz_objects_types.obj_type_lcode, biz_objects_types.obj_type_id, namespaces.namespace_lcode
  • Join Filter: (biz_objects_types.namespace_id = namespaces.namespace_id)
  • Rows Removed by Join Filter: 30
41. 0.005 0.005 ↓ 5.0 10 1

Seq Scan on ng_commons.biz_objects_types (cost=0.00..1.02 rows=2 width=14) (actual time=0.004..0.005 rows=10 loops=1)

  • Output: biz_objects_types.obj_type_id, biz_objects_types.obj_type_lcode, biz_objects_types.namespace_id
42. 0.006 0.010 ↓ 4.0 4 10

Materialize (cost=0.00..1.01 rows=1 width=13) (actual time=0.001..0.001 rows=4 loops=10)

  • Output: namespaces.namespace_lcode, namespaces.namespace_id
43. 0.004 0.004 ↓ 4.0 4 1

Seq Scan on ng_commons.namespaces (cost=0.00..1.01 rows=1 width=13) (actual time=0.004..0.004 rows=4 loops=1)

  • Output: namespaces.namespace_lcode, namespaces.namespace_id
44. 0.005 0.011 ↓ 1.1 11 1

Hash (cost=1.10..1.10 rows=10 width=11) (actual time=0.011..0.011 rows=11 loops=1)

  • Output: permission_actions.perm_action_lcode, permission_actions.perm_action_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.006 0.006 ↓ 1.1 11 1

Seq Scan on ng_security.permission_actions (cost=0.00..1.10 rows=10 width=11) (actual time=0.005..0.006 rows=11 loops=1)

  • Output: permission_actions.perm_action_lcode, permission_actions.perm_action_id
46. 0.008 0.010 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=9) (actual time=0.010..0.010 rows=2 loops=1)

  • Output: source_types.source_type_lcode, source_types.source_type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.002 0.002 ↑ 1.0 2 1

Seq Scan on ng_security.source_types (cost=0.00..1.02 rows=2 width=9) (actual time=0.002..0.002 rows=2 loops=1)

  • Output: source_types.source_type_lcode, source_types.source_type_id
48. 0.008 0.040 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=11) (actual time=0.040..0.040 rows=2 loops=1)

  • Output: audit_auth_results.auth_result_lcode, audit_auth_results.auth_result_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.032 0.032 ↑ 1.0 2 1

Seq Scan on ng_security.audit_auth_results (cost=0.00..1.02 rows=2 width=11) (actual time=0.032..0.032 rows=2 loops=1)

  • Output: audit_auth_results.auth_result_lcode, audit_auth_results.auth_result_id
50. 0.002 0.007 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.007..0.007 rows=3 loops=1)

  • Output: audit_action_results.aa_res_lcode, audit_action_results.aa_res_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on ng_security.audit_action_results (cost=0.00..1.03 rows=3 width=16) (actual time=0.004..0.005 rows=3 loops=1)

  • Output: audit_action_results.aa_res_lcode, audit_action_results.aa_res_id
52. 0.001 0.008 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=14) (actual time=0.008..0.008 rows=5 loops=1)

  • Output: users.user_display_name, users.user_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on ng_user.users (cost=0.00..1.05 rows=5 width=14) (actual time=0.005..0.007 rows=5 loops=1)

  • Output: users.user_display_name, users.user_id
54. 0.001 0.005 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=12) (actual time=0.005..0.005 rows=2 loops=1)

  • Output: user_auth_credentials.auth_login, user_auth_credentials.user_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on ng_security.user_auth_credentials (cost=0.00..1.02 rows=2 width=12) (actual time=0.004..0.004 rows=2 loops=1)

  • Output: user_auth_credentials.auth_login, user_auth_credentials.user_id
Planning time : 3.664 ms