explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tPnY

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 553,910.015 ↑ 513.3 144 1

Subquery Scan on Custom SQL Query (cost=5,713,383.00..5,716,432.20 rows=73,920 width=223) (actual time=553,908.875..553,910.015 rows=144 loops=1)

2. 3,465.990 553,909.988 ↑ 513.3 144 1

HashAggregate (cost=5,713,383.00..5,715,693.00 rows=73,920 width=223) (actual time=553,908.874..553,909.988 rows=144 loops=1)

  • Group Key: d_customer.customer_type, d_customer_agreement.market_area, common_code.code_name, f_retail_portfolio_fact.supply_month, f_retail_portfolio_fact.supply_year
3. 1,213.617 550,443.998 ↑ 2.6 6,288,232 1

Hash Left Join (cost=720,759.27..5,345,277.22 rows=16,360,257 width=51) (actual time=2,767.891..550,443.998 rows=6,288,232 loops=1)

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
4. 1,545.482 549,230.342 ↑ 2.6 6,288,232 1

Hash Join (cost=720,756.80..5,299,530.65 rows=16,360,257 width=49) (actual time=2,767.840..549,230.342 rows=6,288,232 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
5. 3,950.991 547,682.585 ↑ 1.7 6,288,232 1

Hash Join (cost=714,740.79..5,088,974.23 rows=10,916,753 width=49) (actual time=2,765.541..547,682.585 rows=6,288,232 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
6. 3,520.315 542,473.579 ↑ 1.7 6,288,232 1

Hash Join (cost=547,210.01..4,892,786.94 rows=10,916,753 width=54) (actual time=1,489.996..542,473.579 rows=6,288,232 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
7. 537,843.831 538,704.859 ↑ 1.7 6,288,232 1

Bitmap Heap Scan on f_retail_portfolio_fact (cost=522,609.42..4,839,529.71 rows=10,916,753 width=52) (actual time=1,237.601..538,704.859 rows=6,288,232 loops=1)

  • Recheck Cond: ((supply_year = 2020) AND (effective_to_ts = '4712-12-31 23:59:59'::timestamp without time zone))
  • Heap Blocks: exact=1324390
8. 861.028 861.028 ↑ 1.7 6,288,232 1

Bitmap Index Scan on idx_f_retail_portfolio_fact_timefilter_idx (cost=0.00..519,880.23 rows=10,916,753 width=0) (actual time=861.028..861.028 rows=6,288,232 loops=1)

  • Index Cond: ((supply_year = 2020) AND (effective_to_ts = '4712-12-31 23:59:59'::timestamp without time zone))
9. 162.618 248.405 ↑ 1.0 785,271 1

Hash (cost=14,784.71..14,784.71 rows=785,271 width=18) (actual time=248.405..248.405 rows=785,271 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 50991kB
10. 85.787 85.787 ↑ 1.0 785,271 1

Seq Scan on d_customer (cost=0.00..14,784.71 rows=785,271 width=18) (actual time=0.016..85.787 rows=785,271 loops=1)

11. 668.655 1,258.015 ↑ 1.0 2,951,101 1

Hash (cost=130,642.01..130,642.01 rows=2,951,101 width=11) (actual time=1,258.015..1,258.015 rows=2,951,101 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 171101kB
12. 589.360 589.360 ↑ 1.0 2,951,101 1

Seq Scan on d_customer_agreement (cost=0.00..130,642.01 rows=2,951,101 width=11) (actual time=0.018..589.360 rows=2,951,101 loops=1)

13. 0.186 2.275 ↑ 1.5 1,467 1

Hash (cost=5,988.52..5,988.52 rows=2,200 width=24) (actual time=2.275..2.275 rows=1,467 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 113kB
14. 0.302 2.089 ↑ 1.5 1,467 1

Hash Join (cost=88.50..5,988.52 rows=2,200 width=24) (actual time=0.547..2.089 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
15. 1.333 1.519 ↑ 1.5 1,467 1

Bitmap Heap Scan on retail_product_detail (cost=45.47..5,939.70 rows=2,200 width=24) (actual time=0.270..1.519 rows=1,467 loops=1)

  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=853
16. 0.186 0.186 ↑ 1.5 1,467 1

Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..44.92 rows=2,200 width=0) (actual time=0.186..0.186 rows=1,467 loops=1)

  • Index Cond: (effective_to_ts >= now())
17. 0.126 0.268 ↑ 1.0 1,468 1

Hash (cost=24.68..24.68 rows=1,468 width=8) (actual time=0.268..0.268 rows=1,468 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
18. 0.142 0.142 ↑ 1.0 1,468 1

Seq Scan on retail_product (cost=0.00..24.68 rows=1,468 width=8) (actual time=0.006..0.142 rows=1,468 loops=1)

19. 0.022 0.039 ↑ 1.0 65 1

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

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

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