explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sMQ4

Settings
# exclusive inclusive rows x rows loops node
1. 20,850.722 66,310.941 ↓ 0.0 0 1

Planning time: 2068.139 msInsert on report_daily_by_mailbox_domain_results (cost=536,845.69..588,242.08 rows=478,106 width=1,695) (actual time=66,310.941..66,310.941 rows=0 loops=1)

2. 968.016 45,460.219 ↑ 1.0 477,323 1

Subquery Scan on *SELECT* (cost=536,845.69..588,242.08 rows=478,106 width=1,695) (actual time=43,464.297..45,460.219 rows=477,323 loops=1)

3. 1,830.615 44,492.203 ↑ 1.0 477,323 1

HashAggregate (cost=536,845.69..570,313.11 rows=478,106 width=333) (actual time=43,464.187..44,492.203 rows=477,323 loops=1)

  • Group Key: dbmr.day_id, acct.slug, mb.address, mb.slug, sd.domain, dbmr.action_taken
4. 397.129 42,661.588 ↑ 1.0 477,480 1

Hash Join (cost=84,569.75..516,526.18 rows=478,106 width=144) (actual time=23,529.867..42,661.588 rows=477,480 loops=1)

  • Hash Cond: (dbmr.sender_domain_id = sd.id)
5. 34,593.478 34,593.478 ↑ 1.0 477,480 1

Hash Join (cost=38,638.39..469,339.80 rows=478,106 width=136) (actual time=15,849.780..34,593.478 rows=477,480 loops=1)

6. 0.000 7,670.981 ↓ 1.0 1,337,064 1

Hash (cost=29,258.38..29,258.38 rows=1,333,838 width=24) (actual time=7,670.981..7,670.981 rows=1,337,064 loops=1)

  • Hash Cond: (dbmr.account_id = acct.id)
  • Buckets: 2,097,152 Batches: 1 Memory Usage: 95,258kB
7. 7,111.770 7,111.770 ↓ 1.0 1,337,064 1

Seq Scan on sender_domains sd (cost=0.00..29,258.38 rows=1,333,838 width=24) (actual time=0.008..7,111.770 rows=1,337,064 loops=1)

8. 34,148.389 34,148.389 ↑ 1.0 477,480 1

Hash Join (cost=38,623.45..468,039.44 rows=478,106 width=131) (actual time=15,716.727..34,148.389 rows=477,480 loops=1)

9. 0.000 133.028 ↓ 1.0 176 1

Hash (cost=12.75..12.75 rows=175 width=21) (actual time=133.028..133.028 rows=176 loops=1)

  • Hash Cond: (dbmr.mailbox_id = mb.id)
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
10. 132.967 132.967 ↓ 1.0 176 1

Seq Scan on accounts acct (cost=0.00..12.75 rows=175 width=21) (actual time=0.009..132.967 rows=176 loops=1)

11. 18,063.824 18,063.824 ↑ 1.0 477,480 1

Index Scan using daily_base_message_results_day_id_idx on daily_base_message_results dbmr (cost=0.57..428,161.50 rows=478,106 width=80) (actual time=109.826..18,063.824 rows=477,480 loops=1)

12. 123.435 15,604.596 ↓ 1.0 273,835 1

Hash (cost=35,252.95..35,252.95 rows=269,595 width=67) (actual time=15,604.595..15,604.596 rows=273,835 loops=1)

  • Index Cond: (day_id = 20,200,101)
  • Buckets: 524,288 Batches: 1 Memory Usage: 31,805kB
13. 15,481.161 15,481.161 ↓ 1.0 273,835 1

Seq Scan on mailboxes mb (cost=0.00..35,252.95 rows=269,595 width=67) (actual time=0.519..15,481.161 rows=273,835 loops=1)

Execution time : 66,339.170 ms