explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ygVz

Settings
# exclusive inclusive rows x rows loops node
1. 0.788 592.649 ↓ 2.0 872 1

Sort (cost=32,340.84..32,341.93 rows=436 width=302) (actual time=592.596..592.649 rows=872 loops=1)

  • Sort Key: "cte_ctrlmap_Controls".framework_id, "cte_ctrlmap_Controls".framework_version, "cte_ctrlmap_Controls".id
  • Sort Method: quicksort Memory: 607kB
2.          

CTE cte_ctrlmap_selected_frameworks

3. 0.001 0.031 ↑ 1.0 1 1

Subquery Scan on X (cost=1.12..1.19 rows=1 width=22) (actual time=0.029..0.031 rows=1 loops=1)

  • Filter: (NOT "X".deleted)
  • Rows Removed by Filter: 1
4. 0.003 0.030 ↑ 1.0 2 1

Unique (cost=1.12..1.17 rows=2 width=30) (actual time=0.026..0.030 rows=2 loops=1)

5. 0.014 0.027 ↓ 1.6 8 1

Sort (cost=1.12..1.13 rows=5 width=30) (actual time=0.026..0.027 rows=8 loops=1)

  • Sort Key: (COALESCE(ctrlmap_selected_framework.organisation_key, 1)), ctrlmap_selected_framework.id, ctrlmap_selected_framework.version, ctrlmap_selected_framework.created DESC
  • Sort Method: quicksort Memory: 25kB
6. 0.013 0.013 ↓ 1.6 8 1

Seq Scan on ctrlmap_selected_framework (cost=0.00..1.06 rows=5 width=30) (actual time=0.011..0.013 rows=8 loops=1)

  • Filter: ((organisation_key IS NULL) OR (organisation_key = 1))
7.          

CTE cte_ctrlmap_frameworks

8. 0.001 0.050 ↑ 1.0 1 1

Subquery Scan on X_1 (cost=1.14..1.16 rows=1 width=58) (actual time=0.048..0.050 rows=1 loops=1)

  • Filter: (NOT "X_1".deleted)
9. 0.001 0.049 ↑ 1.0 1 1

Unique (cost=1.14..1.15 rows=1 width=66) (actual time=0.047..0.049 rows=1 loops=1)

10. 0.010 0.048 ↑ 1.0 1 1

Sort (cost=1.14..1.15 rows=1 width=66) (actual time=0.047..0.048 rows=1 loops=1)

  • Sort Key: (COALESCE(ctrlmap_framework.organisation_key, 1)), ctrlmap_framework.id, ctrlmap_framework.version, ctrlmap_framework.created DESC
  • Sort Method: quicksort Memory: 25kB
11. 0.003 0.038 ↑ 1.0 1 1

Nested Loop (cost=0.00..1.13 rows=1 width=66) (actual time=0.035..0.038 rows=1 loops=1)

  • Join Filter: ((ctrlmap_framework.id = cte_ctrlmap_selected_frameworks.id) AND (ctrlmap_framework.version = cte_ctrlmap_selected_frameworks.version))
  • Rows Removed by Join Filter: 3
12. 0.032 0.032 ↑ 1.0 1 1

CTE Scan on cte_ctrlmap_selected_frameworks (cost=0.00..0.02 rows=1 width=68) (actual time=0.030..0.032 rows=1 loops=1)

13. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on ctrlmap_framework (cost=0.00..1.05 rows=4 width=65) (actual time=0.002..0.003 rows=4 loops=1)

  • Filter: ((organisation_key IS NULL) OR (organisation_key = 1))
14.          

CTE cte_ctrlmap_selected_controls

15. 0.317 14.293 ↓ 2.0 872 1

Subquery Scan on X_2 (cost=245.46..286.88 rows=436 width=28) (actual time=12.610..14.293 rows=872 loops=1)

  • Filter: (NOT "X_2".deleted)
16. 1.034 13.976 ↑ 1.0 872 1

Unique (cost=245.46..278.16 rows=872 width=36) (actual time=12.607..13.976 rows=872 loops=1)

17. 11.126 12.942 ↑ 1.0 2,616 1

Sort (cost=245.46..252.00 rows=2,616 width=36) (actual time=12.605..12.942 rows=2,616 loops=1)

  • Sort Key: (COALESCE(ctrlmap_selected_control.organisation_key, 1)), ctrlmap_selected_control.framework_id, ctrlmap_selected_control.framework_version, ctrlmap_selected_control.id, ctrlmap_selected_control.created DESC
  • Sort Method: quicksort Memory: 301kB
18. 1.213 1.816 ↑ 1.0 2,616 1

Nested Loop (cost=0.00..96.96 rows=2,616 width=36) (actual time=0.013..1.816 rows=2,616 loops=1)

  • Join Filter: ((ctrlmap_selected_control.framework_id = cte_ctrlmap_frameworks.id) AND (ctrlmap_selected_control.framework_version = cte_ctrlmap_frameworks.version))
19. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on cte_ctrlmap_frameworks (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.003 rows=1 loops=1)

20. 0.600 0.600 ↑ 1.0 2,616 1

Seq Scan on ctrlmap_selected_control (cost=0.00..57.70 rows=2,616 width=36) (actual time=0.009..0.600 rows=2,616 loops=1)

  • Filter: ((organisation_key IS NULL) OR (organisation_key = 1))
21.          

CTE cte_ctrlmap_selected_domains

22. 0.001 0.034 ↓ 0.0 0 1

Subquery Scan on X_3 (cost=15.51..15.54 rows=1 width=105) (actual time=0.034..0.034 rows=0 loops=1)

  • Filter: (NOT "X_3".deleted)
23. 0.000 0.033 ↓ 0.0 0 1

Unique (cost=15.51..15.53 rows=1 width=113) (actual time=0.033..0.033 rows=0 loops=1)

24. 0.017 0.033 ↓ 0.0 0 1

Sort (cost=15.51..15.52 rows=1 width=113) (actual time=0.033..0.033 rows=0 loops=1)

  • Sort Key: (COALESCE(ctrlmap_selected_domain.organisation_key, 1)), ctrlmap_selected_domain.framework_id, ctrlmap_selected_domain.framework_version, ctrlmap_selected_domain.domain, ctrlmap_selected_domain.created DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.003 0.016 ↓ 0.0 0 1

Nested Loop (cost=8.35..15.50 rows=1 width=113) (actual time=0.016..0.016 rows=0 loops=1)

26. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on cte_ctrlmap_frameworks cte_ctrlmap_frameworks_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.001 rows=1 loops=1)

27. 0.009 0.012 ↓ 0.0 0 1

Bitmap Heap Scan on ctrlmap_selected_domain (cost=8.35..15.47 rows=1 width=113) (actual time=0.012..0.012 rows=0 loops=1)

  • Recheck Cond: (framework_id = cte_ctrlmap_frameworks_1.id)
  • Filter: (((organisation_key IS NULL) OR (organisation_key = 1)) AND (cte_ctrlmap_frameworks_1.version = framework_version))
28. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on ctrlmap_selected_domain_index (cost=0.00..8.35 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (framework_id = cte_ctrlmap_frameworks_1.id)
29.          

CTE cte_ctrlmap_Domains

30. 0.230 5.215 ↓ 2.0 872 1

Subquery Scan on X_4 (cost=87.71..109.51 rows=436 width=53) (actual time=4.682..5.215 rows=872 loops=1)

  • Filter: (NOT "X_4".deleted)
31. 0.237 4.985 ↑ 1.0 872 1

Unique (cost=87.71..100.79 rows=872 width=61) (actual time=4.679..4.985 rows=872 loops=1)

32. 3.617 4.748 ↑ 1.0 872 1

Sort (cost=87.71..89.89 rows=872 width=61) (actual time=4.678..4.748 rows=872 loops=1)

  • Sort Key: (COALESCE(ctrlmap_domain.organisation_key, 1)), ctrlmap_domain.framework_id, ctrlmap_domain.framework_version, ctrlmap_domain.control_id, ctrlmap_domain.domain, ctrlmap_domain.created DESC
  • Sort Method: quicksort Memory: 143kB
33. 0.362 1.131 ↑ 1.0 872 1

Hash Left Join (cost=0.04..45.12 rows=872 width=61) (actual time=0.078..1.131 rows=872 loops=1)

  • Hash Cond: ((ctrlmap_domain.framework_id = cte_ctrlmap_selected_domains.framework_id) AND (ctrlmap_domain.framework_version = cte_ctrlmap_selected_domains.framework_version) AND (ctrlmap_domain.domain = cte_ctrlmap_selected_domains.domain))
34. 0.552 0.734 ↑ 1.0 872 1

Nested Loop (cost=0.00..35.00 rows=872 width=60) (actual time=0.019..0.734 rows=872 loops=1)

  • Join Filter: ((ctrlmap_domain.framework_id = cte_ctrlmap_frameworks_2.id) AND (ctrlmap_domain.framework_version = cte_ctrlmap_frameworks_2.version))
35. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on cte_ctrlmap_frameworks cte_ctrlmap_frameworks_2 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

36. 0.180 0.180 ↑ 1.0 872 1

Seq Scan on ctrlmap_domain (cost=0.00..21.90 rows=872 width=60) (actual time=0.015..0.180 rows=872 loops=1)

  • Filter: ((organisation_key IS NULL) OR (organisation_key = 1))
37. 0.000 0.035 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=100) (actual time=0.035..0.035 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
38. 0.035 0.035 ↓ 0.0 0 1

CTE Scan on cte_ctrlmap_selected_domains (cost=0.00..0.02 rows=1 width=100) (actual time=0.035..0.035 rows=0 loops=1)

39.          

CTE cte_ctrlmap_Controls

40. 0.271 591.042 ↓ 2.0 872 1

Subquery Scan on X_5 (cost=31,857.30..31,898.72 rows=436 width=439) (actual time=589.735..591.042 rows=872 loops=1)

  • Filter: (NOT "X_5".deleted)
41. 0.820 590.771 ↑ 1.0 872 1

Unique (cost=31,857.30..31,890.00 rows=872 width=447) (actual time=589.733..590.771 rows=872 loops=1)

42. 6.775 589.951 ↑ 1.0 2,616 1

Sort (cost=31,857.30..31,863.84 rows=2,616 width=447) (actual time=589.732..589.951 rows=2,616 loops=1)

  • Sort Key: (COALESCE(ctrlmap_control.organisation_key, 1)), ctrlmap_control.framework_id, ctrlmap_control.framework_version, ctrlmap_control.id, ctrlmap_control.created DESC
  • Sort Method: quicksort Memory: 1859kB
43. 2.388 583.176 ↑ 1.0 2,616 1

Nested Loop (cost=234.48..31,708.81 rows=2,616 width=447) (actual time=22.301..583.176 rows=2,616 loops=1)

  • Join Filter: ((ctrlmap_control.framework_id = cte_ctrlmap_frameworks_3.id) AND (ctrlmap_control.framework_version = cte_ctrlmap_frameworks_3.version))
44. 0.050 0.050 ↑ 1.0 1 1

CTE Scan on cte_ctrlmap_frameworks cte_ctrlmap_frameworks_3 (cost=0.00..0.02 rows=1 width=64) (actual time=0.049..0.050 rows=1 loops=1)

45. 2.493 20.914 ↑ 1.0 2,616 1

Hash Right Join (cost=234.48..251.38 rows=2,616 width=418) (actual time=16.346..20.914 rows=2,616 loops=1)

  • Hash Cond: ((cte_ctrlmap_selected_controls.framework_id = ctrlmap_control.framework_id) AND (cte_ctrlmap_selected_controls.framework_version = ctrlmap_control.framework_version) AND (cte_ctrlmap_selected_controls.id = ctrlmap_control.id))
46. 14.706 14.706 ↓ 2.0 872 1

CTE Scan on cte_ctrlmap_selected_controls (cost=0.00..8.72 rows=436 width=100) (actual time=12.613..14.706 rows=872 loops=1)

47. 2.070 3.715 ↑ 1.0 2,616 1

Hash (cost=188.70..188.70 rows=2,616 width=414) (actual time=3.715..3.715 rows=2,616 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1207kB
48. 1.645 1.645 ↑ 1.0 2,616 1

Seq Scan on ctrlmap_control (cost=0.00..188.70 rows=2,616 width=414) (actual time=0.004..1.645 rows=2,616 loops=1)

  • Filter: ((organisation_key IS NULL) OR (organisation_key = 1))
49.          

SubPlan (for Nested Loop)

50. 7.848 559.824 ↑ 1.0 1 2,616

Aggregate (cost=12.00..12.01 rows=1 width=32) (actual time=0.214..0.214 rows=1 loops=2,616)

51. 551.976 551.976 ↑ 1.0 1 2,616

CTE Scan on "cte_ctrlmap_Domains" (cost=0.00..11.99 rows=1 width=32) (actual time=0.101..0.211 rows=1 loops=2,616)

  • Filter: ((framework_id = ctrlmap_control.framework_id) AND (framework_version = ctrlmap_control.framework_version) AND (control_id = ctrlmap_control.id))
  • Rows Removed by Filter: 871
52. 591.861 591.861 ↓ 2.0 872 1

CTE Scan on "cte_ctrlmap_Controls" (cost=0.00..8.72 rows=436 width=302) (actual time=589.738..591.861 rows=872 loops=1)

Planning time : 1.057 ms
Execution time : 592.990 ms