explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CWkwB

Settings
# exclusive inclusive rows x rows loops node
1. 30.088 24,305.169 ↓ 1.1 29,045 1

Finalize GroupAggregate (cost=2,088,020.87..2,089,126.03 rows=27,629 width=216) (actual time=24,272.269..24,305.169 rows=29,045 loops=1)

  • Group Key: seg_fact.db, date_dim.yyyy, date_dim.mm, date_dim.iw, tbs_dim.tbs, owner_dim.owner, seg_fact.segment_type, seg_fact.used_or_free
2. 242.836 24,275.081 ↓ 1.1 29,045 1

Sort (cost=2,088,020.87..2,088,089.95 rows=27,629 width=216) (actual time=24,272.252..24,275.081 rows=29,045 loops=1)

  • Sort Key: date_dim.yyyy, date_dim.mm, date_dim.iw, tbs_dim.tbs, owner_dim.owner, seg_fact.segment_type, seg_fact.used_or_free
  • Sort Method: quicksort Memory: 4958kB
3. 12.385 24,032.245 ↓ 1.1 29,045 1

Hash Join (cost=463,833.30..2,085,982.70 rows=27,629 width=216) (actual time=5,992.199..24,032.245 rows=29,045 loops=1)

  • Hash Cond: ((seg_fact.owner)::text = (owner_dim.owner)::text)
4. 18.144 24,019.717 ↓ 1.1 29,045 1

Hash Join (cost=463,826.39..2,085,901.85 rows=27,629 width=241) (actual time=5,992.042..24,019.717 rows=29,045 loops=1)

  • Hash Cond: ((seg_fact.tbs)::text = (tbs_dim.tbs)::text)
5. 11,219.525 24,000.604 ↓ 1.1 29,045 1

Nested Loop (cost=463,751.86..2,085,754.67 rows=27,629 width=217) (actual time=5,991.047..24,000.604 rows=29,045 loops=1)

  • Join Filter: (seg_fact.date = date_dim.date)
  • Rows Removed by Join Filter: 179376602
6. 2,942.988 6,066.217 ↓ 1.1 87,206 1

Partial HashAggregate (cost=463,751.86..464,764.67 rows=81,025 width=201) (actual time=5,989.549..6,066.217 rows=87,206 loops=1)

  • Group Key: seg_fact.db, seg_fact.segment_type, seg_fact.used_or_free, seg_fact.date, seg_fact.tbs, seg_fact.owner
7. 3,123.229 3,123.229 ↓ 66.5 5,512,892 1

Seq Scan on seg_fact (cost=0.00..462,301.72 rows=82,865 width=201) (actual time=3.214..3,123.229 rows=5,512,892 loops=1)

  • Filter: ((db)::text = 'RFDMGBL2'::text)
  • Rows Removed by Filter: 11060046
8. 6,713.459 6,714.862 ↓ 1.5 2,057 87,206

Materialize (cost=0.00..82.68 rows=1,334 width=28) (actual time=0.000..0.077 rows=2,057 loops=87,206)

9. 1.403 1.403 ↓ 1.7 2,274 1

Seq Scan on date_dim (cost=0.00..76.01 rows=1,334 width=28) (actual time=0.020..1.403 rows=2,274 loops=1)

  • Filter: ((yyyy)::text >= '2019'::text)
  • Rows Removed by Filter: 1727
10. 0.683 0.969 ↑ 1.0 2,557 1

Hash (cost=42.57..42.57 rows=2,557 width=57) (actual time=0.969..0.969 rows=2,557 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 170kB
11. 0.286 0.286 ↑ 1.0 2,557 1

Seq Scan on tbs_dim (cost=0.00..42.57 rows=2,557 width=57) (actual time=0.019..0.286 rows=2,557 loops=1)

12. 0.063 0.143 ↑ 1.0 218 1

Hash (cost=4.18..4.18 rows=218 width=57) (actual time=0.143..0.143 rows=218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
13. 0.080 0.080 ↑ 1.0 218 1

Seq Scan on owner_dim (cost=0.00..4.18 rows=218 width=57) (actual time=0.059..0.080 rows=218 loops=1)

Planning time : 0.775 ms
Execution time : 24,311.288 ms