explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fT9X

Settings
# exclusive inclusive rows x rows loops node
1. 21,578.120 433,711.781 ↑ 29.8 1,427,101 1

GroupAggregate (cost=19,472,817.33..23,514,173.28 rows=42,540,589 width=363) (actual time=377,671.761..433,711.781 rows=1,427,101 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. 147,377.420 412,133.661 ↑ 1.7 25,517,910 1

Sort (cost=19,472,817.33..19,579,168.80 rows=42,540,589 width=141) (actual time=377,671.718..412,133.661 rows=25,517,910 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: external merge Disk: 2923232kB
3. 7,868.155 264,756.241 ↑ 1.7 25,517,910 1

Hash Join (cost=198,162.89..11,028,975.18 rows=42,540,589 width=141) (actual time=5,214.815..264,756.241 rows=25,517,910 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 18,201.278 256,885.292 ↑ 1.2 25,517,910 1

Hash Join (cost=192,089.70..10,271,843.24 rows=30,119,979 width=97) (actual time=5,211.985..256,885.292 rows=25,517,910 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 12,368.768 236,869.496 ↑ 1.2 25,517,910 1

Hash Join (cost=24,604.40..9,614,908.28 rows=30,119,979 width=76) (actual time=3,380.701..236,869.496 rows=25,517,910 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
6. 224,210.159 224,210.159 ↑ 1.2 25,517,910 1

Seq Scan on f_retail_portfolio_fact (cost=0.00..9,176,154.17 rows=30,119,979 width=74) (actual time=3,083.321..224,210.159 rows=25,517,910 loops=1)

  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 261303523
7. 197.112 290.569 ↑ 1.0 785,271 1

Hash (cost=14,786.40..14,786.40 rows=785,440 width=18) (actual time=290.569..290.569 rows=785,271 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 50991kB
8. 93.457 93.457 ↑ 1.0 785,271 1

Seq Scan on d_customer (cost=0.00..14,786.40 rows=785,440 width=18) (actual time=0.022..93.457 rows=785,271 loops=1)

9. 902.002 1,814.518 ↓ 1.0 2,951,101 1

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

  • Buckets: 4194304 Batches: 1 Memory Usage: 242235kB
10. 912.516 912.516 ↓ 1.0 2,951,101 1

Seq Scan on d_customer_agreement (cost=0.00..130,621.80 rows=2,949,080 width=37) (actual time=0.015..912.516 rows=2,951,101 loops=1)

11. 0.272 2.794 ↑ 1.4 1,467 1

Hash (cost=6,047.50..6,047.50 rows=2,055 width=36) (actual time=2.794..2.794 rows=1,467 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 136kB
12. 0.249 2.522 ↑ 1.4 1,467 1

Hash Left Join (cost=364.01..6,047.50 rows=2,055 width=36) (actual time=0.802..2.522 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.product_category_ccid = common_code_1.common_code_id)
13. 0.254 2.252 ↑ 1.4 1,467 1

Hash Left Join (cost=361.55..6,016.80 rows=2,055 width=34) (actual time=0.772..2.252 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
14. 0.349 1.966 ↑ 1.4 1,467 1

Hash Join (cost=359.08..5,986.10 rows=2,055 width=32) (actual time=0.730..1.966 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
15. 0.981 1.321 ↑ 1.4 1,467 1

Bitmap Heap Scan on retail_product_detail (cost=316.35..5,915.11 rows=2,055 width=24) (actual time=0.424..1.321 rows=1,467 loops=1)

  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=853
16. 0.340 0.340 ↑ 1.4 1,467 1

Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..315.83 rows=2,055 width=0) (actual time=0.340..0.340 rows=1,467 loops=1)

  • Index Cond: (effective_to_ts >= now())
17. 0.161 0.296 ↓ 1.0 1,468 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
18. 0.135 0.135 ↓ 1.0 1,468 1

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

19. 0.014 0.032 ↑ 1.0 65 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
20. 0.018 0.018 ↑ 1.0 65 1

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

21. 0.012 0.021 ↑ 1.0 65 1

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

  • Buckets: 1024 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.002..0.009 rows=65 loops=1)

Planning time : 57.073 ms
Execution time : 434,117.169 ms