explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZHMN

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 34,815.008 ↓ 243.0 243 1

Group (cost=4,745.24..4,745.26 rows=1 width=50) (actual time=34,814.936..34,815.008 rows=243 loops=1)

  • Group Key: metrics.date, metrics.entry_metric, metrics.exit_metric, investments.id, companies.id
2. 0.437 34,814.949 ↓ 243.0 243 1

Sort (cost=4,745.24..4,745.24 rows=1 width=14) (actual time=34,814.934..34,814.949 rows=243 loops=1)

  • Sort Key: metrics.date, metrics.entry_metric, metrics.exit_metric, investments.id, companies.id
  • Sort Method: quicksort Memory: 36kB
3. 10,210.601 34,814.512 ↓ 243.0 243 1

Nested Loop (cost=801.52..4,745.23 rows=1 width=14) (actual time=77.803..34,814.512 rows=243 loops=1)

  • Join Filter: (CASE WHEN ((metrics.associated_type)::text = 'Investment'::text) THEN investments.company_id ELSE metrics.associated_id END = companies.id)
  • Rows Removed by Join Filter: 20687085
4. 3,854.853 3,916.583 ↓ 173,843.1 20,687,328 1

Hash Join (cost=801.23..4,700.45 rows=119 width=22) (actual time=46.717..3,916.583 rows=20,687,328 loops=1)

  • Hash Cond: ((metrics.labeled_as)::text = (metric_definitions.labeled_as)::text)
5. 15.313 17.921 ↓ 1.9 10,544 1

Bitmap Heap Scan on metrics (cost=777.68..4,655.47 rows=5,414 width=31) (actual time=2.900..17.921 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.608 2.608 ↑ 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.608..2.608 rows=33,583 loops=1)

  • Index Cond: ((associated_type)::text = 'Investment'::text)
7. 6.105 43.809 ↓ 1,962.0 15,696 1

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

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

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

9. 2.896 12.816 ↓ 1,962.0 15,696 1

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

10. 0.328 2.072 ↓ 981.0 1,962 1

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

11. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: (id = 7439)
12. 1.732 1.732 ↓ 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.010..1.732 rows=1,962 loops=1)

  • Index Cond: (financial_metrics_set_id = metric_definition_set_items_1.metric_definition_set_id)
13. 7.848 7.848 ↓ 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.004 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)