explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tZde : Optimization for: plan #95KD

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.258 45,525.411 ↓ 2.4 243 1

ProjectSet (cost=4,755.26..4,755.79 rows=100 width=114) (actual time=45,524.981..45,525.411 rows=243 loops=1)

2. 0.153 45,525.153 ↓ 243.0 243 1

Group (cost=4,755.26..4,755.29 rows=1 width=82) (actual time=45,524.973..45,525.153 rows=243 loops=1)

  • Group Key: metric_definition_set_items.periods, (CASE WHEN (metrics.entry_metric OR metrics.exit_metric) THEN NULL::date ELSE metrics.date END), metrics.entry_metric, metrics.exit_metric, investments.id, ('Investment'::text), metric_definition_set_items.metric_type
3. 0.286 45,525.000 ↓ 243.0 243 1

Sort (cost=4,755.26..4,755.27 rows=1 width=82) (actual time=45,524.971..45,525.000 rows=243 loops=1)

  • Sort Key: metric_definition_set_items.periods, (CASE WHEN (metrics.entry_metric OR metrics.exit_metric) THEN NULL::date ELSE metrics.date END), metrics.entry_metric, metrics.exit_metric, investments.id, ('Investment'::text), metric_definition_set_items.metric_type
  • Sort Method: quicksort Memory: 59kB
4. 0.131 45,524.714 ↓ 243.0 243 1

Nested Loop (cost=4,746.66..4,755.25 rows=1 width=82) (actual time=45,523.127..45,524.714 rows=243 loops=1)

5. 0.342 45,524.097 ↓ 243.0 243 1

Nested Loop (cost=4,746.38..4,754.43 rows=1 width=46) (actual time=45,523.118..45,524.097 rows=243 loops=1)

6. 0.132 45,523.269 ↓ 243.0 243 1

Group (cost=4,746.10..4,746.12 rows=1 width=50) (actual time=45,523.105..45,523.269 rows=243 loops=1)

  • Group Key: metrics.date, metrics.entry_metric, metrics.exit_metric, investments.id, companies_1.id
7. 0.509 45,523.137 ↓ 243.0 243 1

Sort (cost=4,746.10..4,746.10 rows=1 width=14) (actual time=45,523.103..45,523.137 rows=243 loops=1)

  • Sort Key: metrics.date, metrics.entry_metric, metrics.exit_metric, investments.id, companies_1.id
  • Sort Method: quicksort Memory: 36kB
8. 19,362.274 45,522.628 ↓ 243.0 243 1

Nested Loop (cost=802.84..4,746.09 rows=1 width=14) (actual time=81.225..45,522.628 rows=243 loops=1)

  • Join Filter: ((investments.company_id = companies_1.id) OR (((metrics.associated_type)::text = 'Company'::text) AND (metrics.associated_id = companies_1.id)))
  • Rows Removed by Join Filter: 20687085
9. 5,402.991 5,473.026 ↓ 176,814.8 20,687,328 1

Hash Join (cost=802.55..4,701.77 rows=117 width=22) (actual time=47.422..5,473.026 rows=20,687,328 loops=1)

  • Hash Cond: ((metrics.labeled_as)::text = (metric_definitions.labeled_as)::text)
10. 22.819 24.943 ↓ 1.9 10,544 1

Bitmap Heap Scan on metrics (cost=777.68..4,655.47 rows=5,414 width=31) (actual time=2.318..24.943 rows=10,544 loops=1)

  • Recheck Cond: ((associated_type)::text = 'Investment'::text)
  • Filter: (((metric_type)::text = 'Enterprise Value'::text) OR ((metric_type)::text = 'EBITDA'::text))
  • Rows Removed by Filter: 23039
  • Heap Blocks: exact=1494
11. 2.124 2.124 ↓ 1.0 34,374 1

Bitmap Index Scan on index_metrics_on_associated_type_and_associated_id (cost=0.00..776.33 rows=33,588 width=0) (actual time=2.124..2.124 rows=34,374 loops=1)

  • Index Cond: ((associated_type)::text = 'Investment'::text)
12. 3.726 45.092 ↓ 1,962.0 15,696 1

Hash (cost=24.77..24.77 rows=8 width=23) (actual time=45.091..45.092 rows=15,696 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 845kB
13. 7.736 41.366 ↓ 1,962.0 15,696 1

Nested Loop (cost=20.60..24.77 rows=8 width=23) (actual time=2.377..41.366 rows=15,696 loops=1)

14. 4.171 17.934 ↓ 1,962.0 15,696 1

Nested Loop (cost=20.33..22.29 rows=8 width=8) (actual time=2.369..17.934 rows=15,696 loops=1)

15. 0.913 7.877 ↓ 981.0 1,962 1

Nested Loop (cost=20.04..21.07 rows=2 width=8) (actual time=2.359..7.877 rows=1,962 loops=1)

16. 0.564 3.040 ↓ 981.0 1,962 1

Unique (cost=19.76..19.77 rows=2 width=4) (actual time=2.301..3.040 rows=1,962 loops=1)

17. 1.019 2.476 ↓ 981.0 1,962 1

Sort (cost=19.76..19.77 rows=2 width=4) (actual time=2.200..2.476 rows=1,962 loops=1)

  • Sort Key: companies_2.id
  • Sort Method: quicksort Memory: 140kB
18. 0.416 1.457 ↓ 981.0 1,962 1

Nested Loop (cost=0.56..19.75 rows=2 width=4) (actual time=0.030..1.457 rows=1,962 loops=1)

19. 0.015 0.015 ↑ 1.0 1 1

Index Scan using metric_definition_set_items_pkey on metric_definition_set_items metric_definition_set_items_2 (cost=0.28..8.30 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: (id = 7439)
20. 1.026 1.026 ↓ 981.0 1,962 1

Index Scan using index_companies_on_financial_metrics_set_id on companies companies_2 (cost=0.28..11.43 rows=2 width=8) (actual time=0.013..1.026 rows=1,962 loops=1)

  • Index Cond: (financial_metrics_set_id = metric_definition_set_items_2.metric_definition_set_id)
21. 3.924 3.924 ↑ 1.0 1 1,962

Index Scan using companies_pkey on companies companies_1 (cost=0.28..0.65 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,962)

  • Index Cond: (id = companies_2.id)
22. 5.886 5.886 ↓ 1.1 8 1,962

Index Scan using index_metric_definition_set_items_on_metric_definition_set_id on metric_definition_set_items metric_definition_set_items_1 (cost=0.28..0.54 rows=7 width=8) (actual time=0.001..0.003 rows=8 loops=1,962)

  • Index Cond: (metric_definition_set_id = companies_1.financial_metrics_set_id)
23. 15.696 15.696 ↑ 1.0 1 15,696

Index Scan using metric_definitions_pkey on metric_definitions (cost=0.28..0.31 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=15,696)

  • Index Cond: (id = metric_definition_set_items_1.metric_definition_id)
24. 20,687.328 20,687.328 ↑ 1.0 1 20,687,328

Index Scan using investments_pkey on investments (cost=0.28..0.36 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=20,687,328)

  • Index Cond: (id = metrics.associated_id)
25. 0.486 0.486 ↑ 1.0 1 243

Index Scan using companies_pkey on companies (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=243)

  • Index Cond: (id = companies_1.id)
26. 0.486 0.486 ↑ 1.0 1 243

Index Scan using index_metric_definition_set_items_on_metric_definition_set_id on metric_definition_set_items (cost=0.28..0.55 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=243)

  • Index Cond: (metric_definition_set_id = companies.financial_metrics_set_id)
  • Filter: (id = 7439)