explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YFpL

Settings
# exclusive inclusive rows x rows loops node
1. 169,004.850 282,195.116 ↑ 3.6 47 1

GroupAggregate (cost=8,480,197.98..9,256,350.82 rows=168 width=112) (actual time=109,161.391..282,195.116 rows=47 loops=1)

  • Group Key: dim_offer.external_id, dim_offer.offer_name
  • Group Key: ()
2. 59,172.929 113,190.266 ↓ 1.1 21,416,262 1

Sort (cost=8,480,197.98..8,528,707.16 rows=19,403,674 width=73) (actual time=107,312.895..113,190.266 rows=21,416,262 loops=1)

  • Sort Key: dim_offer.external_id, dim_offer.offer_name
  • Sort Method: external merge Disk: 1,566,744kB
3. 4,197.933 54,017.337 ↓ 1.1 21,416,262 1

Hash Join (cost=891,759.13..3,544,872.07 rows=19,403,674 width=73) (actual time=9,083.775..54,017.337 rows=21,416,262 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.offer_id = dim_offer.id)
4. 11,345.314 49,819.325 ↓ 1.1 21,601,747 1

Hash Left Join (cost=891,752.90..3,492,520.16 rows=19,503,793 width=45) (actual time=8,800.683..49,819.325 rows=21,601,747 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.person_id = dim_person.id)
5. 13,985.306 36,385.119 ↓ 1.1 21,601,747 1

Hash Left Join (cost=674,628.14..2,917,734.93 rows=19,503,793 width=26) (actual time=6,702.414..36,385.119 rows=21,601,747 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.lead_id = dim_lead.source_id)
6. 1,920.719 15,837.728 ↓ 1.1 21,601,747 1

Append (cost=0.00..1,845,302.32 rows=19,503,793 width=30) (actual time=0.024..15,837.728 rows=21,601,747 loops=1)

7. 13,917.009 13,917.009 ↓ 1.1 21,601,747 1

Seq Scan on facts_distribution_test_2020_05_01 (cost=0.00..1,747,783.35 rows=19,503,793 width=30) (actual time=0.023..13,917.009 rows=21,601,747 loops=1)

  • Filter: ((created_at >= '2020-06-01 00:00:00'::timestamp without time zone) AND (created_at <= '2020-06-30 00:00:00'::timestamp without time zone) AND ((type)::text = 'post'::text))
  • Rows Removed by Filter: 28,947,876
8. 3,366.752 6,562.085 ↓ 1.0 16,412,004 1

Hash (cost=389,996.73..389,996.73 rows=16,374,273 width=16) (actual time=6,562.085..6,562.085 rows=16,412,004 loops=1)

  • Buckets: 131,072 Batches: 512 Memory Usage: 2,433kB
9. 3,195.333 3,195.333 ↓ 1.0 16,412,004 1

Seq Scan on dim_lead (cost=0.00..389,996.73 rows=16,374,273 width=16) (actual time=0.003..3,195.333 rows=16,412,004 loops=1)

10. 1,295.691 2,088.892 ↑ 1.0 5,810,207 1

Hash (cost=104,523.67..104,523.67 rows=5,823,367 width=27) (actual time=2,088.892..2,088.892 rows=5,810,207 loops=1)

  • Buckets: 65,536 Batches: 128 Memory Usage: 3,222kB
11. 793.201 793.201 ↑ 1.0 5,810,207 1

Seq Scan on dim_person (cost=0.00..104,523.67 rows=5,823,367 width=27) (actual time=0.006..793.201 rows=5,810,207 loops=1)

12. 0.039 0.079 ↑ 1.0 188 1

Hash (cost=3.88..3.88 rows=188 width=40) (actual time=0.078..0.079 rows=188 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
13. 0.040 0.040 ↑ 1.0 188 1

Seq Scan on dim_offer (cost=0.00..3.88 rows=188 width=40) (actual time=0.009..0.040 rows=188 loops=1)

  • Filter: (external_id IS NOT NULL)
Planning time : 1.293 ms
Execution time : 282,450.065 ms