explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VZ9N

Settings
# exclusive inclusive rows x rows loops node
1. 21,448.643 450,939.545 ↑ 29.8 1,403,216 1

GroupAggregate (cost=19,327,016.61..23,299,285.14 rows=41,813,353 width=363) (actual time=362,378.875..450,939.545 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. 174,351.527 429,490.902 ↑ 1.6 25,525,204 1

Sort (cost=19,327,016.61..19,431,549.99 rows=41,813,353 width=141) (actual time=362,378.837..429,490.902 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,797.310 255,139.375 ↑ 1.6 25,525,204 1

Hash Join (cost=196,984.47..11,032,723.57 rows=41,813,353 width=141) (actual time=5,965.563..255,139.375 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 17,246.006 247,339.076 ↑ 1.2 25,525,204 1

Hash Join (cost=192,064.86..10,286,417.42 rows=30,449,664 width=97) (actual time=5,962.553..247,339.076 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 10,968.786 228,481.187 ↑ 1.2 25,525,204 1

Hash Join (cost=24,579.56..9,624,125.08 rows=30,449,664 width=76) (actual time=4,333.653..228,481.187 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
6. 217,245.604 217,245.604 ↑ 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=4,063.957..217,245.604 rows=25,525,204 loops=1)

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

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

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

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

9. 800.457 1,611.883 ↑ 1.0 2,949,080 1

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

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

11. 0.244 2.989 ↑ 1.4 1,454 1

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

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

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

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

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

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

Hash Join (cost=222.64..4,834.79 rows=1,998 width=32) (actual time=0.685..2.200 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.360..1.502rows=1,454 loops=1)
  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=239
15. 0.334 0.334 ↑ 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.334..0.334 rows=1,493 loops=1)

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
17. 0.154 0.154 ↑ 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.154 rows=1,455 loops=1)

18. 0.015 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
19. 0.008 0.008 ↑ 1.0 65 1

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

20. 0.018 0.022 ↑ 1.0 65 1

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

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