explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GPC1

Settings
# exclusive inclusive rows x rows loops node
1. 21,405.404 488,541.381 ↑ 30.0 1,403,216 1

GroupAggregate (cost=19,406,142.05..23,399,018.46 rows=42,030,278 width=363) (actual time=395,486.009..488,541.381 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, commo
2. 180,022.034 467,135.977 ↑ 1.6 25,525,204 1

Sort (cost=19,406,142.05..19,511,217.75 rows=42,030,278 width=141) (actual time=395,485.944..467,135.977 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,
  • Sort Method: external merge Disk: 2923768kB
3. 7,699.885 287,113.943 ↑ 1.6 25,525,204 1

Hash Join (cost=196,984.47..11,067,248.72 rows=42,030,278 width=141) (actual time=9,586.430..287,113.943 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 16,538.802 279,411.065 ↑ 1.2 25,525,204 1

Hash Join (cost=192,064.86..10,317,096.31 rows=30,607,635 width=97) (actual time=9,583.415..279,411.065 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 259,614.084 261,312.585 ↑ 1.2 25,525,204 1

Hash Join (cost=24,579.56..9,652,236.94 rows=30,607,635 width=76) (actual time=8,014.372..261,312.585 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
  • -> Seq Scan on f_retail_portfolio_fact (cost=0.00..9206802.40 rows=30607635 width=74) (actual time=6313.606..248490.273 rows=
  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 261267869
6. 174.412 1,698.501 ↑ 1.0 784,647 1

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

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

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

8. 1,559.678 1,559.678 ↑ 1.0 2,949,080 1

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

  • Buckets: 4194304 Batches: 1 Memory Usage: 242088kB
  • -> Seq Scan on d_customer_agreement (cost=0.00..130621.80 rows=2949080 width=37) (actual time=0.013..797.041 rows=2949080 loo
9. 0.251 2.993 ↑ 1.4 1,454 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
10. 0.266 2.742 ↑ 1.4 1,454 1

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

  • Hash Cond: (retail_product_detail.product_category_ccid = common_code_1.common_code_id)
11. 0.252 2.453 ↑ 1.4 1,454 1

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

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
12. 1.857 2.177 ↑ 1.4 1,454 1

Hash Join (cost=222.64..4,834.79 rows=1,998 width=32) (actual time=0.707..2.177 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=1998 width=24) (actual time=0.377..1.505
  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=239
  • -> Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..179.41 rows=1998 width=0) (actual time=
  • Index Cond: (effective_to_ts >= now())
13. 0.320 0.320 ↑ 1.0 1,455 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
  • -> Seq Scan on retail_product (cost=0.00..24.55 rows=1455 width=8) (actual time=0.007..0.158 rows=1455 loop
14. 0.011 0.024 ↑ 1.0 65 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
15. 0.013 0.013 ↑ 1.0 65 1

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

16. 0.014 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: 1024 Batches: 1 Memory Usage: 12kB
17. 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)