explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gOL

Settings
# exclusive inclusive rows x rows loops node
1. 21,521.835 478,725.240 ↑ 30.2 1,403,216 1

GroupAggregate (cost=19,496,438.05..23,526,809.54 rows=42,424,963 width=363) (actual time=392,481.832..478,725.240 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_s
2. 170,462.047 457,203.405 ↑ 1.7 25,525,204 1

Sort (cost=19,496,438.05..19,602,500.46 rows=42,424,963 width=141) (actual time=392,481.780..457,203.405 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.act
  • Sort Method: external merge Disk: 2923768kB
3. 7,555.250 286,741.358 ↑ 1.7 25,525,204 1

Hash Join (cost=197,015.75..11,076,378.51 rows=42,424,963 width=141) (actual time=7,842.592..286,741.358 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 14,536.386 279,180.649 ↑ 1.2 25,525,204 1

Hash Join (cost=192,064.86..10,320,231.15 rows=30,619,207 width=97) (actual time=7,837.114..279,180.649 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 9,999.888 263,032.087 ↑ 1.2 25,525,204 1

Hash Join (cost=24,579.56..9,655,183.73 rows=30,619,207 width=76) (actual time=6,218.438..263,032.087 rows=25,525,204 loops=1)

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

Seq Scan on f_retail_portfolio_fact (cost=0.00..9,209,590.08 rows=30,619,207 width=74) (actual time=5,947.132..252,763.440 rows=25,525,204 loops=1)

  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 261267869
7. 168.615 268.759 ↑ 1.0 784,647 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 50957kB
8. 100.144 100.144 ↑ 1.0 784,647 1

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

9. 731.258 1,612.176 ↑ 1.0 2,949,080 1

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

  • Buckets: 4194304 Batches: 1 Memory Usage: 242088kB
10. 880.918 880.918 ↑ 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.017..880.918 rows=2,949,080 loops=1)

11. 0.268 5.459 ↑ 1.4 1,454 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
12. 0.296 5.191 ↑ 1.4 1,454 1

Hash Left Join (cost=231.71..4,925.69 rows=2,016 width=36) (actual time=0.969..5.191 rows=1,454 loops=1)

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

Hash Left Join (cost=229.24..4,895.53 rows=2,016 width=34) (actual time=0.948..4.879 rows=1,454 loops=1)

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

Hash Join (cost=226.78..4,865.36 rows=2,016 width=32) (actual time=0.913..4.563 rows=1,454 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
15. 3.129 3.645 ↑ 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.583..3.645 rows=1,454 loops=1)

  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=651
16. 0.516 0.516 ↑ 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.516..0.516 rows=1,509 loops=1)

  • Index Cond: (effective_to_ts >= now())
17. 0.122 0.322 ↑ 1.0 1,455 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
18. 0.200 0.200 ↑ 1.0 1,455 1

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

19. 0.011 0.027 ↑ 1.0 65 1

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

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

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

21. 0.009 0.016 ↑ 1.0 65 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
22. 0.007 0.007 ↑ 1.0 65 1

Seq Scan on common_code common_code_1 (cost=0.00..1.65 rows=65 width=18) (actual time=0.003..0.007 rows=65 loops=1)