explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GPC1

Settings
# exclusive inclusive rows x rows loops node
1. 21,405.404 488,541.381 ↑ 30.0 1,403,216 1

GroupAggregate (cost=19,406,142.05..23,399,018.46 rows=42,030,278 width=363) (actual time=395,486.009..488,541.381 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. 180,022.034 467,135.977 ↑ 1.6 25,525,204 1

Sort (cost=19,406,142.05..19,511,217.75 rows=42,030,278 width=141) (actual time=395,485.944..467,135.977 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,768kB
3. 7,699.885 287,113.943 ↑ 1.6 25,525,204 1

Hash Join (cost=196,984.47..11,067,248.72 rows=42,030,278 width=141) (actual time=9,586.430..287,113.943 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 16,538.802 279,411.065 ↑ 1.2 25,525,204 1

Hash Join (cost=192,064.86..10,317,096.31 rows=30,607,635 width=97) (actual time=9,583.415..279,411.065 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 11,123.811 261,312.585 ↑ 1.2 25,525,204 1

Hash Join (cost=24,579.56..9,652,236.94 rows=30,607,635 width=76) (actual time=8,014.372..261,312.585 rows=25,525,204 loops=1)

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

Seq Scan on f_retail_portfolio_fact (cost=0.00..9,206,802.40 rows=30,607,635 width=74) (actual time=6,313.606..248,490.273 rows=25,525,204 loops=1)

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

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

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

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

9. 762.637 1,559.678 ↑ 1.0 2,949,080 1

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

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 242,088kB
10. 797.041 797.041 ↑ 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=0.013..797.041 rows=2,949,080 loops=1)

11. 0.251 2.993 ↑ 1.4 1,454 1

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

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

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

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

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

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

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

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
  • -> Bitmap Heap Scan on retail_product_detail (cost=179.90..4764.58 rows=1,998 width=24) (actual time=0.377..1.505rows=1,454 loops=1)
  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=239
15. 0.350 0.350 ↑ 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=0.350..0.350 rows=1,493 loops=1)

  • Index Cond: (effective_to_ts >= now())
16. 0.162 0.320 ↑ 1.0 1,455 1

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

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

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

18. 0.011 0.024 ↑ 1.0 65 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
19. 0.013 0.013 ↑ 1.0 65 1

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

20. 0.014 0.023 ↑ 1.0 65 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
21. 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.002..0.009 rows=65 loops=1)