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, co
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_nam
  • Sort Method: external merge Disk: 2923768kB
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. 1,150,842.701 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)
  • -> Bitmap Heap Scan on f_retail_portfolio_fact (cost=13745833.85..18433721.97 rows=30619207 width=74) (actual time=878490.780..1139309.225 rows=2
  • Recheck Cond: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Index Recheck: 106346834
  • Heap Blocks: exact=2378136 lossy=1693115
  • -> Bitmap Index Scan on idx_f_retail_portfolio_fact_timefilter_idx (cost=0.00..13738179.05 rows=30619207 width=0) (actual time=877369.332..
  • Index Cond: ((supply_year > 2017) AND (effective_to_ts >= now()))
6. 388.379 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: 1048576 Batches: 1 Memory Usage: 50957kB
  • -> Index Scan using mv_d_customer_customerid_idx on d_customer (cost=0.42..1207848.88 rows=784647 width=18) (actual time=0.017..218.677 row
7. 2,763.395 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: 4194304 Batches: 1 Memory Usage: 242088kB
  • -> Index Scan using idx_d_customer_agreement_idx on d_customer_agreement (cost=0.43..9386610.39 rows=2949080 width=37) (actual time=0.021..2000.0
8. 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: 2048 Batches: 1 Memory Usage: 119kB
9. 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)
10. 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)
11. 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)
12. 3.224 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
  • -> Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..183.54 rows=2016 width=0) (actual time=0.403..0.403 rows=15
  • Index Cond: (effective_to_ts >= now())
13. 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: 2048 Batches: 1 Memory Usage: 73kB
  • -> Index Only Scan using pk_retail_product_id on retail_product (cost=0.28..59.10 rows=1455 width=8) (actual time=0.019..1.736
  • Heap Fetches: 1455
14. 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: 1024 Batches: 1 Memory Usage: 12kB
15. 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)

16. 0.027 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: 1024 Batches: 1 Memory Usage: 12kB
  • -> 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 lo