explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VZ9N

Settings
# exclusive inclusive rows x rows loops node
1. 21,448.643 450,939.545 ↑ 29.8 1,403,216 1

GroupAggregate (cost=19,327,016.61..23,299,285.14 rows=41,813,353 width=363) (actual time=362,378.875..450,939.545 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. 174,351.527 429,490.902 ↑ 1.6 25,525,204 1

Sort (cost=19,327,016.61..19,431,549.99 rows=41,813,353 width=141) (actual time=362,378.837..429,490.902 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,797.310 255,139.375 ↑ 1.6 25,525,204 1

Hash Join (cost=196,984.47..11,032,723.57 rows=41,813,353 width=141) (actual time=5,965.563..255,139.375 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 17,246.006 247,339.076 ↑ 1.2 25,525,204 1

Hash Join (cost=192,064.86..10,286,417.42 rows=30,449,664 width=97) (actual time=5,962.553..247,339.076 rows=25,525,204 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 228,214.390 228,481.187 ↑ 1.2 25,525,204 1

Hash Join (cost=24,579.56..9,624,125.08 rows=30,449,664 width=76) (actual time=4,333.653..228,481.187 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..9180862.64 rows=30449664 width=74) (actual time=4063.957..217245.604 rows=
  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 261267869
6. 168.293 266.797 ↑ 1.0 784,647 1

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

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

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

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

Hash (cost=130,621.80..130,621.80 rows=2,949,080 width=37) (actual time=1,611.883..1,611.883 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..811.426 rows=2949080 loo
9. 0.244 2.989 ↑ 1.4 1,454 1

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

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

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

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

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

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

Hash Join (cost=222.64..4,834.79 rows=1,998 width=32) (actual time=0.685..2.200 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.360..1.502
  • 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.314 0.314 ↑ 1.0 1,455 1

Hash (cost=24.55..24.55 rows=1,455 width=8) (actual time=0.314..0.314 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.154 rows=1455 loop
14. 0.015 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
15. 0.008 0.008 ↑ 1.0 65 1

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

16. 0.018 0.022 ↑ 1.0 65 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
17. 0.004 0.004 ↑ 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.004 rows=65 loops=1)