explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w8Kj

Settings
# exclusive inclusive rows x rows loops node
1. 167,986.969 282,776.292 ↑ 3.6 47 1

GroupAggregate (cost=8,481,624.61..9,257,777.45 rows=168 width=112) (actual time=110,424.852..282,776.292 rows=47 loops=1)

  • Group Key: dim_offer.external_id, dim_offer.offer_name
  • Group Key: ()
2. 59,577.648 114,789.323 ↓ 1.1 21,416,262 1

Sort (cost=8,481,624.61..8,530,133.79 rows=19,403,674 width=73) (actual time=108,991.363..114,789.323 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,312.003 55,211.675 ↓ 1.1 21,416,262 1

Hash Join (cost=892,967.76..3,546,298.70 rows=19,403,674 width=73) (actual time=9,369.367..55,211.675 rows=21,416,262 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.offer_id = dim_offer.id)
4. 11,524.517 50,899.570 ↓ 1.1 21,601,747 1

Hash Left Join (cost=892,961.53..3,493,946.79 rows=19,503,793 width=45) (actual time=9,094.835..50,899.570 rows=21,601,747 loops=1)

  • Hash Cond: (facts_distribution_test_2020_05_01.person_id = dim_person.id)
5. 14,449.007 37,001.504 ↓ 1.1 21,601,747 1

Hash Left Join (cost=675,339.74..2,918,573.52 rows=19,503,793 width=26) (actual time=6,711.632..37,001.504 rows=21,601,747 loops=1)

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

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

7. 13,792.183 13,792.183 ↓ 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.026..13,792.183 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,439.268 6,704.033 ↓ 1.0 16,423,335 1

Hash (cost=390,256.55..390,256.55 rows=16,400,255 width=16) (actual time=6,704.033..6,704.033 rows=16,423,335 loops=1)

  • Buckets: 131,072 Batches: 512 Memory Usage: 2,434kB
9. 3,264.765 3,264.765 ↓ 1.0 16,423,335 1

Seq Scan on dim_lead (cost=0.00..390,256.55 rows=16,400,255 width=16) (actual time=0.003..3,264.765 rows=16,423,335 loops=1)

10. 1,521.469 2,373.549 ↑ 1.0 5,819,095 1

Hash (cost=104,763.02..104,763.02 rows=5,836,702 width=27) (actual time=2,373.549..2,373.549 rows=5,819,095 loops=1)

  • Buckets: 65,536 Batches: 128 Memory Usage: 3,226kB
11. 852.080 852.080 ↑ 1.0 5,819,095 1

Seq Scan on dim_person (cost=0.00..104,763.02 rows=5,836,702 width=27) (actual time=0.006..852.080 rows=5,819,095 loops=1)

12. 0.043 0.102 ↑ 1.0 188 1

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

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

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

  • Filter: (external_id IS NOT NULL)
Planning time : 1.421 ms
Execution time : 283,040.904 ms