explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JhZg

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.552 ↓ 0.0 0 1

GroupAggregate (cost=1,837,866.09..1,839,083.61 rows=12,816 width=362) (actual time=0.552..0.552 rows=0 loops=1)

  • Group Key: f_retail_portfolio_fact.supply_year, f_retail_portfolio_fact.supply_month, d_customer.customer_type, d_customer_agreement.market_area, common_code.code_name, common_code_1.code_name, d_customer_agreement.agreement_type, d_customer_agreement.actual_supply_start_date, d_customer_agreement.actual_supply_end_date, (CASE WHEN ((d_customer.customer_type)::text = ANY ('{Microbusiness,Household}'::text[])) THEN 'B2C'::character varying WHEN ((d_customer.customer_type)::text = ANY ('{SME,Corporate}'::text[])) THEN 'B2B'::character varying ELSE d_customer.customer_type END)
  • d_customer_agreement.agreement_type, d_customer_agreement.actual_supply_start_date, d_customer_agreement.actual_supply_end_date, (CASE WHEN ((d_customer.customer_type)::text = ANY ('{Microbusiness,Household}'::text[])) THEN 'B2C'::character varying WHEN ((d_customer.customer_type)::text = ANY ('{SME,Corporate}'::text[])) THEN 'B2B'::character varying ELSE d_customer.customer_type END)
2. 0.049 0.551 ↓ 0.0 0 1

Sort (cost=1,837,866.09..1,837,898.13 rows=12,816 width=177) (actual time=0.551..0.551 rows=0 loops=1)

  • Sort Key: f_retail_portfolio_fact.supply_year, f_retail_portfolio_fact.supply_month, d_customer.customer_type, d_customer_agreement.market_area, common_code.code_name, common_code_1.code_name,
  • Sort Method: quicksort Memory: 25kB
3. 0.001 0.502 ↓ 0.0 0 1

Nested Loop (cost=13.22..1,836,991.68 rows=12,816 width=177) (actual time=0.502..0.502 rows=0 loops=1)

4. 0.000 0.501 ↓ 0.0 0 1

Nested Loop (cost=12.79..1,830,953.42 rows=12,816 width=125) (actual time=0.501..0.501 rows=0 loops=1)

5. 0.000 0.501 ↓ 0.0 0 1

Nested Loop (cost=12.36..1,825,129.92 rows=12,816 width=123) (actual time=0.501..0.501 rows=0 loops=1)

  • Join Filter: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
6. 0.011 0.501 ↓ 0.0 0 1

Hash Join (cost=12.09..1,821,186.21 rows=12,825 width=139) (actual time=0.501..0.501 rows=0 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product_detail.retail_product_id)
7. 0.073 0.073 ↑ 18,480,674.0 1 1

Seq Scan on f_retail_portfolio_fact (cost=0.00..1,751,743.34 rows=18,480,674 width=111) (actual time=0.073..0.073 rows=1 loops=1)

  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 672
8. 0.000 0.417 ↓ 0.0 0 1

Hash (cost=12.07..12.07 rows=1 width=28) (actual time=0.417..0.417 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
9. 0.013 0.417 ↓ 0.0 0 1

Hash Join (cost=10.20..12.07 rows=1 width=28) (actual time=0.417..0.417 rows=0 loops=1)

  • Hash Cond: (common_code_1.common_code_id = retail_product_detail.product_category_ccid)
10. 0.005 0.005 ↑ 63.0 1 1

Seq Scan on common_code common_code_1 (cost=0.00..1.63 rows=63 width=18) (actual time=0.005..0.005 rows=1 loops=1)

11. 0.001 0.399 ↓ 0.0 0 1

Hash (cost=10.19..10.19 rows=1 width=22) (actual time=0.399..0.399 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
12. 0.361 0.398 ↓ 0.0 0 1

Hash Join (cost=8.31..10.19 rows=1 width=22) (actual time=0.398..0.398 rows=0 loops=1)

  • Hash Cond: (common_code.common_code_id = retail_product_detail.service_category_ccid)
13. 0.001 0.001 ↑ 63.0 1 1

Seq Scan on common_code (cost=0.00..1.63 rows=63 width=18) (actual time=0.001..0.001 rows=1 loops=1)

14. 0.000 0.036 ↓ 0.0 0 1

Hash (cost=8.30..8.30 rows=1 width=16) (actual time=0.036..0.036 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
15. 0.036 0.036 ↓ 0.0 0 1

Index Scan using idx_retail_product_detail_eff_idx on retail_product_detail (cost=0.28..8.30 rows=1 width=16) (actual time=0.036..0.036 rows=0 loops=1)

  • Index Cond: (effective_to_ts >= now())
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_retail_product_id on retail_product (cost=0.28..0.30 rows=1 width=8) (never executed)

  • Index Cond: (retail_product_id = f_retail_portfolio_fact.retail_product_id)
  • Heap Fetches: 0
17. 0.000 0.000 ↓ 0.0 0

Index Scan using mv_d_customer_customerid_idx on d_customer (cost=0.42..0.44 rows=1 width=18) (never executed)

  • Index Cond: (customer_id = f_retail_portfolio_fact.customer_id)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_d_customer_agreement_idx on d_customer_agreement (cost=0.43..0.46 rows=1 width=36) (never executed)

  • Index Cond: (customer_agreement_id = f_retail_portfolio_fact.customer_agreement_id)