explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eXcI

Settings
# exclusive inclusive rows x rows loops node
1. 4,869.663 597,089.762 ↑ 41.3 392,259 1

GroupAggregate (cost=7,345,424.10..8,884,620.85 rows=16,202,071 width=363) (actual time=591,155.340..597,089.762 rows=392,259 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_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)
2. 32,419.722 592,220.099 ↑ 2.4 6,641,028 1

Sort (cost=7,345,424.10..7,385,929.28 rows=16,202,071 width=141) (actual time=591,155.303..592,220.099 rows=6,641,028 loops=1)

  • Sort Key: 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: 1564701kB
3. 3,083.587 559,800.377 ↑ 2.4 6,641,028 1

Hash Join (cost=711,476.08..5,405,252.44 rows=16,202,071 width=141) (actual time=4,753.171..559,800.377 rows=6,641,028 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.retail_product_id = retail_product.retail_product_id)
4. 6,793.066 556,713.626 ↑ 1.6 6,641,028 1

Hash Join (cost=705,407.57..5,102,535.55 rows=10,723,463 width=97) (actual time=4,749.986..556,713.626 rows=6,641,028 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_agreement_id = d_customer_agreement.customer_agreement_id)
5. 5,347.007 548,180.577 ↑ 1.6 6,641,028 1

Hash Join (cost=537,876.79..4,906,855.64 rows=10,723,463 width=76) (actual time=3,004.733..548,180.577 rows=6,641,028 loops=1)

  • Hash Cond: (f_retail_portfolio_fact.customer_id = d_customer.customer_id)
6. 540,319.889 542,483.609 ↑ 1.6 6,641,028 1

Bitmap Heap Scan on f_retail_portfolio_fact (cost=513,276.20..4,854,105.80 rows=10,723,463 width=74) (actual time=2,650.613..542,483.609 rows=6,641,028 loops=1)

  • Recheck Cond: ((supply_year = 2019) AND (effective_to_ts >= now()))
  • Heap Blocks: exact=1435841
7. 2,163.720 2,163.720 ↑ 1.6 6,641,028 1

Bitmap Index Scan on idx_f_retail_portfolio_fact_timefilter_idx (cost=0.00..510,595.33 rows=10,723,463 width=0) (actual time=2,163.720..2,163.720 rows=6,641,028 loops=1)

  • Index Cond: ((supply_year = 2019) AND (effective_to_ts >= now()))
8. 251.008 349.961 ↑ 1.0 785,271 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 50991kB
9. 98.953 98.953 ↑ 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..98.953 rows=785,271 loops=1)

10. 893.909 1,739.983 ↑ 1.0 2,951,101 1

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

  • Buckets: 4194304 Batches: 1 Memory Usage: 242235kB
11. 846.074 846.074 ↑ 1.0 2,951,101 1

Seq Scan on d_customer_agreement (cost=0.00..130,642.01 rows=2,951,101 width=37) (actual time=0.021..846.074 rows=2,951,101 loops=1)

12. 0.264 3.164 ↑ 1.5 1,467 1

Hash (cost=6,040.78..6,040.78 rows=2,218 width=36) (actual time=3.164..3.164 rows=1,467 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 136kB
13. 0.213 2.900 ↑ 1.5 1,467 1

Hash Left Join (cost=93.56..6,040.78 rows=2,218 width=36) (actual time=0.736..2.900 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.product_category_ccid = common_code_1.common_code_id)
14. 0.329 2.668 ↑ 1.5 1,467 1

Hash Left Join (cost=91.10..6,032.12 rows=2,218 width=34) (actual time=0.713..2.668 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.service_category_ccid = common_code.common_code_id)
15. 0.380 2.294 ↑ 1.5 1,467 1

Hash Join (cost=88.64..6,023.46 rows=2,218 width=32) (actual time=0.649..2.294 rows=1,467 loops=1)

  • Hash Cond: (retail_product_detail.retail_product_id = retail_product.retail_product_id)
16. 1.422 1.647 ↑ 1.5 1,467 1

Bitmap Heap Scan on retail_product_detail (cost=45.61..5,974.58 rows=2,218 width=24) (actual time=0.373..1.647 rows=1,467 loops=1)

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

Bitmap Index Scan on ixfk_retail_product_effectiveto (cost=0.00..45.05 rows=2,218 width=0) (actual time=0.225..0.225 rows=1,467 loops=1)

  • Index Cond: (effective_to_ts >= now())
18. 0.131 0.267 ↑ 1.0 1,468 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
19. 0.136 0.136 ↑ 1.0 1,468 1

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

20. 0.021 0.045 ↑ 1.0 65 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.024 0.024 ↑ 1.0 65 1

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

22. 0.009 0.019 ↑ 1.0 65 1

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

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