explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MqCP

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 43,302.133 ↓ 243.0 243 1
→

Group (cost=4,745.54..4,745.55 rows=1 width=50) (actual time=43,302.061..43,302.133 rows=243 loops=1)

  • Group Key: metrics.date, metrics.entry_metric, metrics.exit_metric, investments.id, companies.id
2. 0.392 43,302.074 ↓ 243.0 243 1
→

Sort (cost=4,745.54..4,745.54 rows=1 width=14) (actual time=43,302.059..43,302.074 rows=243 loops=1)

  • Sort Key: metrics.date, metrics.entry_metric, metrics.exit_metric, investments.id, companies.id
  • Sort Method: quicksort Memory: 36kB
3. 17,494.395 43,301.682 ↓ 243.0 243 1
→

Nested Loop (cost=801.52..4,745.53 rows=1 width=14) (actual time=73.840..43,301.682 rows=243 loops=1)

  • Join Filter: ((investments.company_id = companies.id) OR (((metrics.associated_type)::text = 'Company'::text) AND (metrics.associated_id = companies.id)))
  • Rows Removed by Join Filter: 20687085
4. 5,062.961 5,119.959 ↓ 173,843.1 20,687,328 1
→

Hash Join (cost=801.23..4,700.45 rows=119 width=22) (actual time=38.630..5,119.959 rows=20,687,328 loops=1)

  • Hash Cond: ((metrics.labeled_as)::text = (metric_definitions.labeled_as)::text)
5. 18.695 21.096 ↓ 1.9 10,544 1
→

Bitmap Heap Scan on metrics (cost=777.68..4,655.47 rows=5,414 width=31) (actual time=2.717..21.096 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: 23034
  • Heap Blocks: exact=1484
6. 2.401 2.401 ↑ 1.0 33,583 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.401..2.401 rows=33,583 loops=1)

  • Index Cond: ((associated_type)::text = 'Investment'::text)
7. 3.364 35.902 ↓ 1,962.0 15,696 1
→

Hash (cost=23.45..23.45 rows=8 width=23) (actual time=35.901..35.902 rows=15,696 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 845kB
8. 5.871 32.538 ↓ 1,962.0 15,696 1
→

Nested Loop (cost=1.12..23.45 rows=8 width=23) (actual time=0.043..32.538 rows=15,696 loops=1)

9. 3.291 10.971 ↓ 1,962.0 15,696 1
→

Nested Loop (cost=0.84..20.97 rows=8 width=8) (actual time=0.036..10.971 rows=15,696 loops=1)

10. 0.301 1.794 ↓ 981.0 1,962 1
→

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

11. 0.016 0.016 ↑ 1.0 1 1
→

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

  • Index Cond: (id = 7439)
12. 1.477 1.477 ↓ 981.0 1,962 1
→

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

  • Index Cond: (financial_metrics_set_id = metric_definition_set_items_1.metric_definition_set_id)
13. 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 (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.financial_metrics_set_id)
14. 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.metric_definition_id)
15. 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)