explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1tus

Settings
# exclusive inclusive rows x rows loops node
1. 20,889.365 414,845.552 ↑ 29.8 1,403,216 1

GroupAggregate (cost=19,327,016.61..23,299,285.14 rows=41,813,353 width=363) (actual time=372,757.768..414,845.552 rows=1,403,216 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)
2. 139,057.151 393,956.187 ↑ 1.6 25,525,204 1

Sort (cost=19,327,016.61..19,431,549.99 rows=41,813,353 width=141) (actual time=372,757.739..393,956.187 rows=25,525,204 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, 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)
  • Sort Method: external merge Disk: 2,923,936kB
3. 7,709.597 254,899.036 ↑ 1.6 25,525,204 1

Hash Join (cost=196,984.47..11,032,723.57 rows=41,813,353 width=141) (actual time=11,316.405..254,899.036 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 15,584.221 246,904.350 ↑ 1.2 25,525,204 1

Hash Join (cost=192,064.86..10,286,417.42 rows=30,449,664 width=97) (actual time=11,031.296..246,904.350 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 10,265.763 226,002.066 ↑ 1.2 25,525,204 1

Hash Join (cost=24,579.56..9,624,125.08 rows=30,449,664 width=76) (actual time=5,693.506..226,002.066 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
6. 215,132.409 215,132.409 ↑ 1.2 25,525,204 1

Seq Scan on f_retail_portfolio_fact (cost=0.00..9,180,862.64 rows=30,449,664 width=74) (actual time=5,086.336..215,132.409 rows=25,525,204 loops=1)

  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 261,267,869
7. 185.497 603.894 ↑ 1.0 784,647 1

Hash (cost=14,771.47..14,771.47 rows=784,647 width=18) (actual time=603.894..603.894 rows=784,647 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 50,957kB
8. 418.397 418.397 ↑ 1.0 784,647 1

Seq Scan on d_customer (cost=0.00..14,771.47 rows=784,647 width=18) (actual time=0.952..418.397 rows=784,647 loops=1)

9. 845.027 5,318.063 ↑ 1.0 2,949,080 1

Hash (cost=130,621.80..130,621.80 rows=2,949,080 width=37) (actual time=5,318.063..5,318.063 rows=2,949,080 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 242,088kB
10. 4,473.036 4,473.036 ↑ 1.0 2,949,080 1

Seq Scan on d_customer_agreement (cost=0.00..130,621.80 rows=2,949,080 width=37) (actual time=12.800..4,473.036 rows=2,949,080 loops=1)

11. 0.348 285.089 ↑ 1.4 1,454 1

Hash (cost=4,894.64..4,894.64 rows=1,998 width=36) (actual time=285.089..285.089 rows=1,454 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 119kB
12. 0.240 284.741 ↑ 1.4 1,454 1

Hash Left Join (cost=227.57..4,894.64 rows=1,998 width=36) (actual time=27.220..284.741 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.product_category_ccid = common_code_1.common_code_id)
13. 0.277 284.470 ↑ 1.4 1,454 1

Hash Left Join (cost=225.10..4,864.71 rows=1,998 width=34) (actual time=27.168..284.470 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
14. 0.487 283.695 ↑ 1.4 1,454 1

Hash Join (cost=222.64..4,834.79 rows=1,998 width=32) (actual time=26.651..283.695 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
15. 257.200 281.591 ↑ 1.4 1,454 1

Bitmap Heap Scan on retail_product_detail (cost=179.90..4,764.58 rows=1,998 width=24) (actual time=25.015..281.591 rows=1,454 loops=1)

  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=239
16. 24.391 24.391 ↑ 1.3 1,493 1

Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..179.41 rows=1,998 width=0) (actual time=24.391..24.391 rows=1,493 loops=1)

  • Index Cond: (effective_to_ts >= now())
17. 0.181 1.617 ↑ 1.0 1,455 1

Hash (cost=24.55..24.55 rows=1,455 width=8) (actual time=1.617..1.617 rows=1,455 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
18. 1.436 1.436 ↑ 1.0 1,455 1

Seq Scan on retail_product (cost=0.00..24.55 rows=1,455 width=8) (actual time=0.006..1.436 rows=1,455 loops=1)

19. 0.015 0.498 ↑ 1.0 65 1

Hash (cost=1.65..1.65 rows=65 width=18) (actual time=0.498..0.498 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
20. 0.483 0.483 ↑ 1.0 65 1

Seq Scan on common_code (cost=0.00..1.65 rows=65 width=18) (actual time=0.475..0.483 rows=65 loops=1)

21. 0.022 0.031 ↑ 1.0 65 1

Hash (cost=1.65..1.65 rows=65 width=18) (actual time=0.031..0.031 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
22. 0.009 0.009 ↑ 1.0 65 1

Seq Scan on common_code common_code_1 (cost=0.00..1.65 rows=65 width=18) (actual time=0.003..0.009 rows=65 loops=1)