explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fXX6G

Settings
# exclusive inclusive rows x rows loops node
1. 21,736.963 1,382,767.402 ↑ 30.2 1,403,216 1

GroupAggregate (cost=39,169,693.44..43,200,064.93 rows=42,424,963 width=363) (actual time=1,287,816.754..1,382,767.402 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. 183,766.605 1,361,030.439 ↑ 1.7 25,525,204 1

Sort (cost=39,169,693.44..39,275,755.85 rows=42,424,963 width=141) (actual time=1,287,816.714..1,361,030.439 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,955.878 1,177,263.834 ↑ 1.7 25,525,204 1

Hash Join (cost=24,391,973.09..30,749,633.90 rows=42,424,963 width=141) (actual time=881,665.150..1,177,263.834 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 15,300.102 1,169,294.577 ↑ 1.2 25,525,204 1

Hash Join (cost=24,386,964.72..29,993,429.05 rows=30,619,207 width=97) (actual time=881,651.747..1,169,294.577 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 11,533.476 1,151,231.080 ↑ 1.2 25,525,204 1

Hash Join (cost=14,963,490.82..20,072,393.04 rows=30,619,207 width=76) (actual time=878,881.949..1,151,231.080 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
6. 261,939.893 1,139,309.225 ↑ 1.2 25,525,204 1

Bitmap Heap Scan on f_retail_portfolio_fact (cost=13,745,833.85..18,433,721.97 rows=30,619,207 width=74) (actual time=878,490.780..1,139,309.225 rows=25,525,204 loops=1)

  • Recheck Cond: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Index Recheck: 106,346,834
  • Heap Blocks: exact=2,378,136 lossy=1,693,115
7. 877,369.332 877,369.332 ↑ 1.1 27,754,988 1

Bitmap Index Scan on idx_f_retail_portfolio_fact_timefilter_idx (cost=0.00..13,738,179.05 rows=30,619,207 width=0) (actual time=877,369.332..877,369.332 rows=27,754,988 loops=1)

  • Index Cond: ((supply_year > 2017) AND (effective_to_ts >= now()))
8. 169.702 388.379 ↑ 1.0 784,647 1

Hash (cost=1,207,848.88..1,207,848.88 rows=784,647 width=18) (actual time=388.379..388.379 rows=784,647 loops=1)

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

Index Scan using mv_d_customer_customerid_idx on d_customer (cost=0.42..1,207,848.88 rows=784,647 width=18) (actual time=0.017..218.677 rows=784,647 loops=1)

10. 763.305 2,763.395 ↑ 1.0 2,949,080 1

Hash (cost=9,386,610.39..9,386,610.39 rows=2,949,080 width=37) (actual time=2,763.395..2,763.395 rows=2,949,080 loops=1)

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

Index Scan using idx_d_customer_agreement_idx on d_customer_agreement (cost=0.43..9,386,610.39 rows=2,949,080 width=37) (actual time=0.021..2,000.090 rows=2,949,080 loops=1)

12. 0.276 13.379 ↑ 1.4 1,454 1

Hash (cost=4,983.18..4,983.18 rows=2,016 width=36) (actual time=13.379..13.379 rows=1,454 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 119kB
13. 0.277 13.103 ↑ 1.4 1,454 1

Hash Left Join (cost=289.19..4,983.18 rows=2,016 width=36) (actual time=9.333..13.103 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.product_category_ccid = common_code_1.common_code_id)
14. 0.272 12.799 ↑ 1.4 1,454 1

Hash Left Join (cost=275.26..4,941.55 rows=2,016 width=34) (actual time=9.294..12.799 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
15. 0.503 5.631 ↑ 1.4 1,454 1

Hash Join (cost=261.33..4,899.92 rows=2,016 width=32) (actual time=2.384..5.631 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
16. 2.821 3.224 ↑ 1.4 1,454 1

Bitmap Heap Scan on retail_product_detail (cost=184.04..4,794.91 rows=2,016 width=24) (actual time=0.466..3.224 rows=1,454 loops=1)

  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=651
17. 0.403 0.403 ↑ 1.3 1,509 1

Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..183.54 rows=2,016 width=0) (actual time=0.403..0.403 rows=1,509 loops=1)

  • Index Cond: (effective_to_ts >= now())
18. 1.904 1.904 ↑ 1.0 1,455 1

Hash (cost=59.10..59.10 rows=1,455 width=8) (actual time=1.904..1.904 rows=1,455 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
  • -> Index Only Scan using pk_retail_product_id on retail_product (cost=0.28..59.10 rows=1,455 width=8) (actual time=0.019..1.736rows=1,455 loops=1)
  • Heap Fetches: 1,455
19. 0.023 6.896 ↑ 1.0 65 1

Hash (cost=13.12..13.12 rows=65 width=18) (actual time=6.896..6.896 rows=65 loops=1)

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

Index Scan using pk_common_code_id on common_code (cost=0.14..13.12 rows=65 width=18) (actual time=6.861..6.873 rows=65 loops=1)

21. 0.015 0.027 ↑ 1.0 65 1

Hash (cost=13.12..13.12 rows=65 width=18) (actual time=0.027..0.027 rows=65 loops=1)

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

Index Scan using pk_common_code_id on common_code common_code_1 (cost=0.14..13.12 rows=65 width=18) (actual time=0.003..0.012 rows=65 loops=1)