explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hx7G : SELECT store_abbr , sales_outlet_name , sales_outlet_type , sum(dw.fact_sales_daily_flat2.net_amount) AS net_amount FROM dw.fact_sales_daily_flat2 WHERE dw.fact_sales_daily_flat2.trn_date >= '2019-06-08 00:00:00' AND dw.fact_sales_daily_flat2.trn_date <= '2019-07-08 00:00:00' AND dw.fact_sales_daily_flat2.store_abbr IN ('TO') GROUP BY store_abbr , sales_outlet_name , sales_outlet_type

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 52,088.160 ↓ 0.0 0 1

Finalize GroupAggregate (cost=1,456,050.79..1,457,010.45 rows=672 width=50) (actual time=52,088.160..52,088.160 rows=0 loops=1)

  • Group Key: ss.store_abbr, so.sales_outlet_name, so.sales_outlet_type
  • Buffers: shared hit=31,051 read=416,523
2. 35.251 52,113.205 ↓ 0.0 0 1

Gather Merge (cost=1,456,050.79..1,456,985.25 rows=1,344 width=50) (actual time=52,088.157..52,113.205 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=59,514 read=1,261,832
3. 0.003 52,077.954 ↓ 0.0 0 3 / 3

Partial GroupAggregate (cost=1,455,050.77..1,455,830.10 rows=672 width=50) (actual time=52,077.954..52,077.954 rows=0 loops=3)

  • Group Key: ss.store_abbr, so.sales_outlet_name, so.sales_outlet_type
  • Buffers: shared hit=59,514 read=1,261,832
4. 0.100 52,077.951 ↓ 0.0 0 3 / 3

Sort (cost=1,455,050.77..1,455,204.96 rows=61,674 width=24) (actual time=52,077.951..52,077.951 rows=0 loops=3)

  • Sort Key: so.sales_outlet_name, so.sales_outlet_type
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=59,514 read=1,261,832
5. 0.001 52,077.851 ↓ 0.0 0 3 / 3

Hash Left Join (cost=21.03..1,450,143.87 rows=61,674 width=24) (actual time=52,077.851..52,077.851 rows=0 loops=3)

  • Hash Cond: (sd.sales_outlet_key = so.sales_outlet_key)
  • Buffers: shared hit=59,456 read=1,261,832
6. 101.771 52,077.850 ↓ 0.0 0 3 / 3

Hash Join (cost=10.89..1,449,969.32 rows=61,674 width=13) (actual time=52,077.850..52,077.850 rows=0 loops=3)

  • Hash Cond: (sd.sales_sublocation_key = ss.sales_sublocation_key)
  • Buffers: shared hit=59,456 read=1,261,832
7. 51,975.923 51,975.923 ↑ 1.2 409,542 3 / 3

Parallel Seq Scan on fact_sales_daily sd (cost=0.00..1,448,698.39 rows=474,207 width=34) (actual time=9,884.919..51,975.923 rows=409,542 loops=3)

  • Filter: ((trn_date >= '2019-06-08'::date) AND (trn_date <= '2019-07-08'::date))
  • Rows Removed by Filter: 6,951,568
  • Buffers: shared hit=59,416 read=1,261,832
8. 0.019 0.156 ↑ 1.0 45 3 / 3

Hash (cost=10.32..10.32 rows=45 width=7) (actual time=0.156..0.156 rows=45 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=18
9. 0.137 0.137 ↑ 1.0 45 3 / 3

Seq Scan on dim_sales_sublocation ss (cost=0.00..10.32 rows=45 width=7) (actual time=0.118..0.137 rows=45 loops=3)

  • Filter: (store_abbr = 'TO'::text)
  • Rows Removed by Filter: 301
  • Buffers: shared hit=18
10. 0.000 0.000 ↓ 0.0 0 / 3

Hash (cost=6.73..6.73 rows=273 width=19) (never executed)

11. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on dim_sales_outlet so (cost=0.00..6.73 rows=273 width=19) (never executed)

Planning time : 0.950 ms
Execution time : 52,113.319 ms