explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lKSr : w/ fixed dim_time join condition to match the index (trn_time + hour_24)

Settings
# exclusive inclusive rows x rows loops node
1. 4,789.112 79,542.193 ↑ 1.0 1 1

Aggregate (cost=2,761,720.88..2,761,720.91 rows=1 width=200) (actual time=79,542.193..79,542.193 rows=1 loops=1)

  • Buffers: shared hit=29,190 read=2,139,951, temp read=12,561 written=12,591
2. 0.000 74,753.081 ↓ 2.2 5,411,582 1

Gather (cost=1,010.24..2,687,195.53 rows=2,484,178 width=24) (actual time=14.023..74,753.081 rows=5,411,582 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=29,190 read=2,139,951
3. 722.242 75,483.282 ↓ 1.7 1,803,861 3 / 3

Hash Join (cost=10.24..2,437,777.73 rows=1,035,074 width=24) (actual time=113.789..75,483.282 rows=1,803,861 loops=3)

  • Hash Cond: (sd.payment_type_key = pt.payment_type_key)
  • Buffers: shared hit=29,190 read=2,139,951
4. 1,023.396 74,760.972 ↓ 1.8 1,941,058 3 / 3

Hash Join (cost=8.31..2,434,564.20 rows=1,101,612 width=28) (actual time=113.591..74,760.972 rows=1,941,058 loops=3)

  • Hash Cond: (sd.sales_outlet_key = so.sales_outlet_key)
  • Buffers: shared hit=29,083 read=2,139,951
5. 73,737.444 73,737.444 ↑ 1.2 3,495,566 3 / 3

Parallel Seq Scan on fact_sales_component sd (cost=0.00..2,423,585.25 rows=4,115,335 width=60) (actual time=113.408..73,737.444 rows=3,495,566 loops=3)

  • Filter: ((trn_date >= '2019-12-30'::date) AND (trn_date <= '2020-06-21'::date))
  • Rows Removed by Filter: 10,773,244
  • Buffers: shared hit=29,071 read=2,139,951
6. 0.022 0.132 ↑ 1.0 73 3 / 3

Hash (cost=7.40..7.40 rows=73 width=4) (actual time=0.132..0.132 rows=73 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=12
7. 0.110 0.110 ↑ 1.0 73 3 / 3

Seq Scan on dim_sales_outlet so (cost=0.00..7.40 rows=73 width=4) (actual time=0.055..0.110 rows=73 loops=3)

  • Filter: (sales_outlet_type = 'Retail'::text)
  • Rows Removed by Filter: 199
  • Buffers: shared hit=12
8. 0.013 0.068 ↑ 1.0 36 3 / 3

Hash (cost=1.48..1.48 rows=36 width=4) (actual time=0.068..0.068 rows=36 loops=3)

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

Seq Scan on dim_payment_type pt (cost=0.00..1.48 rows=36 width=4) (actual time=0.045..0.055 rows=36 loops=3)

  • Filter: (payment_type_name <> ALL ('{Instacart,""Deposit Redeem""}'::text[]))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=9
Planning time : 1.326 ms
Execution time : 79,542.602 ms