explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J205

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Join (cost=176,282,613.75..312,587,333.89 rows=2,580,414,101 width=208) (actual rows= loops=)

  • Merge Cond: ((company_app_id_totals_mrel.consumed_resource_uri = prep.consumed_resource_uri) AND (company_app_id_totals_mrel.company_id = prep.company_id))
2.          

CTE reference

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,827,358.72..1,954,045.13 rows=3,645,600 width=104) (actual rows= loops=)

  • Group Key: report_info.report_id, discover_raw_mrel.company_id, discover_raw_mrel.consumed_resource_uri
4. 0.000 0.000 ↓ 0.0

Sort (cost=1,827,358.72..1,845,404.80 rows=7,218,433 width=104) (actual rows= loops=)

  • Sort Key: report_info.report_id, discover_raw_mrel.company_id, discover_raw_mrel.consumed_resource_uri
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..215,538.75 rows=7,218,433 width=104) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on report_info (cost=0.00..18.10 rows=810 width=64) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Scan using discover_consumption_type_idx on discover_raw_mrel (cost=0.42..176.95 rows=8,912 width=72) (actual rows= loops=)

  • Index Cond: (consumed_resource_uri = ANY (report_info.app_ids))
8.          

CTE tests

9. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5,793.90..5,976.18 rows=18,228 width=72) (actual rows= loops=)

  • Group Key: discover_raw_mrel_1.company_id, discover_raw_mrel_1.consumed_resource_uri
10. 0.000 0.000 ↓ 0.0

Seq Scan on discover_raw_mrel discover_raw_mrel_1 (cost=0.00..4,426.80 rows=182,280 width=72) (actual rows= loops=)

11.          

CTE prep

12. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=770,949.47..11,578,604.09 rows=332,259,984 width=168) (actual rows= loops=)

  • Merge Cond: (t.company_id = r.company_id)
13. 0.000 0.000 ↓ 0.0

Sort (cost=1,654.54..1,700.11 rows=18,228 width=72) (actual rows= loops=)

  • Sort Key: t.company_id
14. 0.000 0.000 ↓ 0.0

CTE Scan on tests t (cost=0.00..364.56 rows=18,228 width=72) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Materialize (cost=769,294.93..787,522.93 rows=3,645,600 width=72) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=769,294.93..778,408.93 rows=3,645,600 width=72) (actual rows= loops=)

  • Sort Key: r.company_id
17. 0.000 0.000 ↓ 0.0

CTE Scan on reference r (cost=0.00..72,912.00 rows=3,645,600 width=72) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=48,330.94..49,107.56 rows=310,650 width=72) (actual rows= loops=)

  • Sort Key: company_app_id_totals_mrel.consumed_resource_uri, company_app_id_totals_mrel.company_id
19. 0.000 0.000 ↓ 0.0

Seq Scan on company_app_id_totals_mrel (cost=0.00..7,248.50 rows=310,650 width=72) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Materialize (cost=162,695,657.41..164,356,957.33 rows=332,259,984 width=168) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=162,695,657.41..163,526,307.37 rows=332,259,984 width=168) (actual rows= loops=)

  • Sort Key: prep.consumed_resource_uri, prep.company_id
22. 0.000 0.000 ↓ 0.0

CTE Scan on prep (cost=0.00..6,645,199.68 rows=332,259,984 width=168) (actual rows= loops=)