explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gcTs

Settings
# exclusive inclusive rows x rows loops node
1. 154.325 1,346.379 ↓ 1.4 19,782 1

Hash Left Join (cost=1,895.36..15,575.08 rows=14,115 width=17) (actual time=937.018..1,346.379 rows=19,782 loops=1)

  • Hash Cond: ((prioritization_investments.dataset_id = rank_list.dataset_id) AND (prioritization_investments.investment_id = rank_list.investment_id))
2.          

CTE rank_list

3. 148.734 530.824 ↓ 860.1 19,782 1

WindowAgg (cost=435.72..436.12 rows=23 width=26) (actual time=310.325..530.824 rows=19,782 loops=1)

4. 152.248 382.090 ↓ 860.1 19,782 1

Sort (cost=435.72..435.78 rows=23 width=18) (actual time=310.311..382.090 rows=19,782 loops=1)

  • Sort Key: dataset_logic_score_investment_int_value.value
  • Sort Method: quicksort Memory: 2,314kB
5. 153.920 229.842 ↓ 860.1 19,782 1

Hash Join (cost=16.97..435.20 rows=23 width=18) (actual time=0.255..229.842 rows=19,782 loops=1)

  • Hash Cond: (dataset_logic_score_investment_int_value.dataset_logic_score_id = dataset_logic_score.id)
6. 75.699 75.699 ↑ 1.0 19,782 1

Seq Scan on dataset_logic_score_investment_int_value (cost=0.00..343.82 rows=19,782 width=22) (actual time=0.013..75.699 rows=19,782 loops=1)

7. 0.017 0.223 ↑ 1.0 1 1

Hash (cost=16.96..16.96 rows=1 width=8) (actual time=0.219..0.223 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.016 0.206 ↑ 1.0 1 1

Nested Loop (cost=0.76..16.96 rows=1 width=8) (actual time=0.170..0.206 rows=1 loops=1)

9. 0.016 0.164 ↑ 1.0 1 1

Nested Loop (cost=0.61..16.77 rows=1 width=4) (actual time=0.134..0.164 rows=1 loops=1)

10. 0.016 0.133 ↑ 1.0 1 1

Nested Loop (cost=0.45..16.57 rows=1 width=4) (actual time=0.111..0.133 rows=1 loops=1)

  • Join Filter: (prioritization_datasets.logic_id = business_logics.id)
11. 0.023 0.102 ↑ 1.0 1 1

Nested Loop (cost=0.30..16.35 rows=1 width=8) (actual time=0.086..0.102 rows=1 loops=1)

12. 0.018 0.018 ↑ 1.0 1 1

Index Scan using prioritization_4_1_datasets_pkey on prioritization_datasets (cost=0.15..8.17 rows=1 width=36) (actual time=0.010..0.018 rows=1 loops=1)

  • Index Cond: (id = 2)
13. 0.061 0.061 ↑ 1.0 1 1

Index Only Scan using prioritization_4_1_investment_cases_logics_pkey on prioritization_investment_cases_logics (cost=0.15..8.17 rows=1 width=36) (actual time=0.058..0.061 rows=1 loops=1)

  • Index Cond: ((investment_case_id = prioritization_datasets.investment_case_id) AND (logic_id = prioritization_datasets.logic_id))
  • Heap Fetches: 1
14. 0.015 0.015 ↑ 1.0 1 1

Index Scan using business_logics_pkey on business_logics (cost=0.15..0.21 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)

  • Index Cond: (id = prioritization_investment_cases_logics.logic_id)
15. 0.015 0.015 ↑ 1.0 1 1

Index Scan using dataset_logics_pkey on dataset_logics (cost=0.15..0.19 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)

  • Index Cond: (id = business_logics.dataset_logic_id)
16. 0.026 0.026 ↑ 1.0 1 1

Index Only Scan using dataset_logic_score_pkey on dataset_logic_score (cost=0.15..0.19 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=1)

  • Index Cond: (id = dataset_logics.score_logic_id)
  • Heap Fetches: 1
17. 159.166 435.042 ↓ 1.4 19,782 1

Hash Join (cost=1,458.70..15,032.55 rows=14,115 width=13) (actual time=179.962..435.042 rows=19,782 loops=1)

  • Hash Cond: (prioritization_investments.investment_id = prioritization_investment_financial_metrics.investment_id)
18. 96.084 96.084 ↑ 1.0 19,782 1

Seq Scan on prioritization_investments (cost=0.00..13,521.85 rows=19,805 width=13) (actual time=0.041..96.084 rows=19,782 loops=1)

  • Filter: (dataset_id = 2)
  • Rows Removed by Filter: 36,286
19. 86.710 179.792 ↑ 1.0 19,782 1

Hash (cost=1,209.85..1,209.85 rows=19,908 width=13) (actual time=179.789..179.792 rows=19,782 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,184kB
20. 93.082 93.082 ↑ 1.0 19,782 1

Seq Scan on prioritization_investment_financial_metrics (cost=0.00..1,209.85 rows=19,908 width=13) (actual time=0.568..93.082 rows=19,782 loops=1)

  • Filter: (dataset_id = 2)
  • Rows Removed by Filter: 36,286
21. 75.716 757.012 ↓ 19,782.0 19,782 1

Hash (cost=0.52..0.52 rows=1 width=44) (actual time=757.009..757.012 rows=19,782 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,338kB
22. 681.296 681.296 ↓ 19,782.0 19,782 1

CTE Scan on rank_list (cost=0.00..0.52 rows=1 width=44) (actual time=310.335..681.296 rows=19,782 loops=1)

  • Filter: (dataset_id = 2)