explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1dC3

Settings
# exclusive inclusive rows x rows loops node
1. 6,618.973 388,822.967 ↑ 32.5 1,448,639 1

Finalize GroupAggregate (cost=9,108,289.78..19,933,128.14 rows=47,077,340 width=363) (actual time=371,152.862..388,822.967 rows=1,448,639 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_cu
2. 2,910.258 382,203.994 ↑ 17.1 2,298,305 1

Gather Merge (cost=9,108,289.78..15,401,934.21 rows=39,231,116 width=363) (actual time=371,152.827..382,203.994 rows=2,298,305 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 6,981.439 379,293.736 ↑ 25.6 766,102 3 / 3

Partial GroupAggregate (cost=9,107,289.76..10,872,689.98 rows=19,615,558 width=363) (actual time=370,949.062..379,293.736 rows=766,102 loops=3)

  • 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_supply_end_date, (CASE WHEN ((d_customer.customer_type)::text = ANY ('{Microbusiness,Household}'::text[])) THEN 'B2C'::character varying WHEN ((d_customer.customer_type)::text = ANY ('{SME,Corporate}'::text[])) THEN 'B2B'::character varying ELSE d_customer.customer_type END)
4. 39,098.587 372,312.297 ↑ 2.3 8,500,940 3 / 3

Sort (cost=9,107,289.76..9,156,328.66 rows=19,615,558 width=141) (actual time=370,949.009..372,312.297 rows=8,500,940 loops=3)

  • 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.actual_supply_end_date, (CASE WHEN ((d_customer.customer_type)::text = ANY ('{Microbusiness,Household}'::text[])) THEN 'B2C'::character varying WHEN ((d_customer.customer_type)::text = ANY ('{SME,Corporate}'::text[])) THEN 'B2B'::character varying ELSE d_customer.customer_type END)
  • Sort Method: quicksort Memory: 1929234kB
  • Worker 0: Sort Method: quicksort Memory: 1918516kB
  • Worker 1: Sort Method: quicksort Memory: 1923121kB
5. 3,024.032 333,213.710 ↑ 2.3 8,500,940 3 / 3

Parallel Hash Join (cost=149,533.45..6,731,306.75 rows=19,615,558 width=141) (actual time=895.465..333,213.710 rows=8,500,940 loops=3)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
6. 6,159.653 330,188.203 ↑ 1.5 8,500,940 3 / 3

Parallel Hash Join (cost=143,114.16..6,464,773.62 rows=12,444,097 width=97) (actual time=893.838..330,188.203 rows=8,500,940 loops=3)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
7. 3,980.920 323,285.846 ↑ 1.5 8,500,940 3 / 3

Parallel Hash Join (cost=14,316.59..6,303,310.26 rows=12,444,097 width=76) (actual time=143.704..323,285.846 rows=8,500,940 loops=3)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
8. 319,162.965 319,162.965 ↑ 1.5 8,500,940 3 / 3

Parallel Seq Scan on f_retail_portfolio_fact (cost=0.00..6,256,327.73 rows=12,444,097 width=74) (actual time=0.031..319,162.965 rows=8,500,940 loops=3)

  • Filter: ((supply_year > 2017) AND (effective_to_ts >= now()))
  • Rows Removed by Filter: 87243880
9. 105.843 141.961 ↑ 1.3 261,951 3 / 3

Parallel Hash (cost=10,220.15..10,220.15 rows=327,715 width=18) (actual time=141.960..141.961 rows=261,951 loops=3)

  • Buckets: 1048576 Batches: 1 Memory Usage: 51136kB
10. 36.118 36.118 ↑ 1.3 261,951 3 / 3

Parallel Seq Scan on d_customer (cost=0.00..10,220.15 rows=327,715 width=18) (actual time=0.048..36.118 rows=261,951 loops=3)

11. 417.420 742.704 ↑ 1.2 984,444 3 / 3

Parallel Hash (cost=113,427.25..113,427.25 rows=1,229,625 width=37) (actual time=742.703..742.704 rows=984,444 loops=3)

  • Buckets: 4194304 Batches: 1 Memory Usage: 242880kB
12. 325.284 325.284 ↑ 1.2 984,444 3 / 3

Parallel Seq Scan on d_customer_agreement (cost=0.00..113,427.25 rows=1,229,625 width=37) (actual time=0.017..325.284 rows=984,444 loops=3)

13. 0.109 1.475 ↑ 2.8 489 3 / 3

Parallel Hash (cost=6,402.28..6,402.28 rows=1,361 width=36) (actual time=1.475..1.475 rows=489 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
14. 0.071 1.366 ↓ 1.1 1,467 1 / 3

Hash Left Join (cost=294.31..6,402.28 rows=1,361 width=36) (actual time=0.899..4.098 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.product_category_ccid = common_code_1.common_code_id)
15. 0.075 1.290 ↓ 1.1 1,467 1 / 3

Hash Left Join (cost=291.85..6,396.01 rows=1,361 width=34) (actual time=0.876..3.869 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
16. 0.172 1.205 ↓ 1.1 1,467 1 / 3

Hash Join (cost=289.38..6,389.74 rows=1,361 width=32) (actual time=0.843..3.616 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
17. 0.808 0.943 ↓ 1.1 1,467 1 / 3

Parallel Bitmap Heap Scan on retail_product_detail (cost=246.35..6,343.12 rows=1,361 width=24) (actual time=0.564..2.830 rows=1,467 loops=1)

  • Recheck Cond: (effective_to_ts >= now())
  • Heap Blocks: exact=1269
18. 0.135 0.135 ↑ 1.6 1,467 1 / 3

Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..245.77 rows=2,314 width=0) (actual time=0.405..0.405 rows=1,467 loops=1)

  • Index Cond: (effective_to_ts >= now())
19. 0.041 0.090 ↑ 1.0 1,468 1 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
20. 0.048 0.048 ↑ 1.0 1,468 1 / 3

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

21. 0.004 0.009 ↑ 1.0 65 1 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
22. 0.005 0.005 ↑ 1.0 65 1 / 3

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

23. 0.003 0.006 ↑ 1.0 65 1 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
24. 0.003 0.003 ↑ 1.0 65 1 / 3

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