explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7pru

Settings
# exclusive inclusive rows x rows loops node
1. 532.390 204,228.499 ↓ 1.2 2,437,576 1

Unique (cost=16,624,409.96..16,708,869.64 rows=2,111,492 width=62) (actual time=202,343.87..204,228.499 rows=2,437,576 loops=1)

  • Buffers: shared hit=99350374 read=4107410 written=159, temp read=178187 written=178204
2. 940.305 203,696.109 ↓ 1.4 2,864,280 1

Aggregate (cost=16,624,409.96..16,703,590.91 rows=2,111,492 width=62) (actual time=202,343.867..203,696.109 rows=2,864,280 loops=1)

  • Buffers: shared hit=99350374 read=4107410 written=159, temp read=178187 written=178204
3. 1,572.195 202,755.804 ↓ 1.4 2,864,302 1

Sort (cost=16,624,409.96..16,629,688.69 rows=2,111,492 width=52) (actual time=202,343.852..202,755.804 rows=2,864,302 loops=1)

  • Sort Key: fms.mail_sent_id, fmt.link_type_id DESC, fmt.sent_at, fmt.created_at, dhmd.domain_id, mu.country_id
  • Sort Method: external merge Disk: 176784kB
  • Buffers: shared hit=99350374 read=4107410 written=159, temp read=178187 written=178204
4. 567.936 201,183.609 ↓ 1.4 2,864,302 1

Hash Join (cost=11,019,975.81..16,351,047.01 rows=2,111,492 width=52) (actual time=195,478.575..201,183.609 rows=2,864,302 loops=1)

  • Buffers: shared hit=99350365 read=4107410 written=159, temp read=156089 written=156096
5. 3,778.950 200,615.626 ↓ 1.4 2,864,302 1

Hash Join (cost=11,019,969.55..16,337,296.79 rows=2,111,492 width=52) (actual time=195,478.516..200,615.626 rows=2,864,302 loops=1)

  • Buffers: shared hit=99350363 read=4107410 written=159, temp read=156089 written=156096
6. 6,301.061 186,934.380 ↓ 1.4 2,864,302 1

Merge Join (cost=9,872,791.48..14,991,869.05 rows=2,111,492 width=58) (actual time=185,571.972..186,934.38 rows=2,864,302 loops=1)

  • Buffers: shared hit=99350067 read=3786813 written=159, temp read=16104 written=16111
7. 78,818.190 78,818.190 ↑ 1.1 121,532,261 1

Index Scan using fact_mail_tracking_mail_sent_idx on fact_mail_tracking fmt (cost=0.57..5,406,189.74 rows=136,367,293 width=26) (actual time=0.03..78,818.19 rows=121,532,261 loops=1)

  • Filter: (fmt.link_type_id = ANY ('{0,1}'::integer[]))
  • Buffers: shared hit=99272482 read=382609 written=159
8. 1,111.352 101,815.129 ↓ 1.2 2,437,576 1

Sort (cost=9,872,790.91..9,878,069.64 rows=2,111,492 width=40) (actual time=101,524.789..101,815.129 rows=2,437,576 loops=1)

  • Sort Key: fms.mail_sent_id
  • Sort Method: external merge Disk: 128832kB
  • Buffers: shared hit=77585 read=3404204, temp read=16104 written=16111
9. 100,703.777 100,703.777 ↓ 1.2 2,437,576 1

Seq Scan on fact_mail_sent fms (cost=0..9,609,737.96 rows=2,111,492 width=40) (actual time=563.448..100,703.777 rows=2,437,576 loops=1)

  • Filter: (fms.sent_at > date((now() - '7 days'::interval)))
  • Buffers: shared hit=77585 read=3404204
10. 3,998.567 9,902.296 ↓ 1.0 30,190,639 1

Hash (cost=622,645.92..622,645.92 rows=30,175,292 width=10) (actual time=9,902.296..9,902.296 rows=30,190,639 loops=1)

  • Buffers: shared hit=296 read=320597, temp written=113879
11. 5,903.729 5,903.729 ↓ 1.0 30,190,639 1

Seq Scan on mail_user mu (cost=0..622,645.92 rows=30,175,292 width=10) (actual time=0.025..5,903.729 rows=30,190,639 loops=1)

  • Buffers: shared hit=296 read=320597
12. 0.020 0.047 ↑ 1.0 189 1

Hash (cost=3.89..3.89 rows=189 width=8) (actual time=0.047..0.047 rows=189 loops=1)

  • Buffers: shared hit=2
13. 0.027 0.027 ↑ 1.0 189 1

Seq Scan on domain_has_mail_domain dhmd (cost=0..3.89 rows=189 width=8) (actual time=0.009..0.027 rows=189 loops=1)

  • Buffers: shared hit=2