explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CVrL

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 315,523.842 ↓ 25.0 25 1

Limit (cost=261,903.07..261,903.07 rows=1 width=474) (actual time=315,523.787..315,523.842 rows=25 loops=1)

2. 26.355 315,523.802 ↓ 25.0 25 1

Sort (cost=261,903.07..261,903.07 rows=1 width=474) (actual time=315,523.783..315,523.802 rows=25 loops=1)

  • Sort Key: msgs.time_iso DESC
  • Sort Method: top-N heapsort Memory: 36kB
3. 16.544 315,497.447 ↓ 856.0 856 1

Nested Loop Left Join (cost=148,876.39..261,903.06 rows=1 width=474) (actual time=266,235.945..315,497.447 rows=856 loops=1)

  • Join Filter: (msgs.scanner_id = virus_scanners.id)
  • Rows Removed by Join Filter: 7704
4. 10.598 315,468.919 ↓ 856.0 856 1

Nested Loop Left Join (cost=148,876.39..261,901.85 rows=1 width=476) (actual time=266,235.882..315,468.919 rows=856 loops=1)

  • Join Filter: (msgs.virus_id = viruses.id)
  • Rows Removed by Join Filter: 4280
5. 6.719 315,450.617 ↓ 856.0 856 1

Nested Loop Left Join (cost=148,876.39..261,900.74 rows=1 width=453) (actual time=266,235.826..315,450.617 rows=856 loops=1)

  • Join Filter: (msgs.rbl_id = rbls.id)
6. 482.892 315,443.042 ↓ 856.0 856 1

Nested Loop Left Join (cost=148,876.39..261,880.39 rows=1 width=312) (actual time=266,235.812..315,443.042 rows=856 loops=1)

  • Filter: (((recip.domain)::text = 'com.24hourflex'::text) OR (((sender.domain)::text = 'com.24hourflex'::text) AND (msgs.originating <> 'N'::bpchar)))
  • Rows Removed by Filter: 94367
7. 752.909 313,722.251 ↓ 8.8 95,223 1

Hash Left Join (cost=148,875.96..256,796.23 rows=10,849 width=291) (actual time=263,925.141..313,722.251 rows=95,223 loops=1)

  • Hash Cond: (msgs.sid = sender.id)
8. 21,062.391 296,338.576 ↓ 8.8 95,223 1

Hash Right Join (cost=131,689.41..235,017.50 rows=10,849 width=246) (actual time=247,292.154..296,338.576 rows=95,223 loops=1)

  • Hash Cond: ((msgrcpt.mail_id)::text = (msgs.mail_id)::text)
  • Filter: ((msgrcpt.content <> '?'::bpchar) OR ((msgs.policy)::text = ANY ('{QUARANTINE.IN,QUARANTINE.OUT,CF.REJECT,CF.REJECT.IN,CF.REJECT.OUT,CF.DISCARD,CF.DISCARD.IN,CF.DISCARD.OUT}'::text[])))
9. 28,164.710 28,164.710 ↑ 1.1 2,021,721 1

Seq Scan on msgrcpt (cost=0.00..94,944.94 rows=2,170,394 width=113) (actual time=0.068..28,164.710 rows=2,021,721 loops=1)

10. 517.093 247,111.475 ↓ 8.9 93,058 1

Hash (cost=131,559.11..131,559.11 rows=10,424 width=146) (actual time=247,111.475..247,111.475 rows=93,058 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 2 (originally 1) Memory Usage: 9729kB
11. 246,594.382 246,594.382 ↓ 8.9 93,058 1

Seq Scan on msgs (cost=0.00..131,559.11 rows=10,424 width=146) (actual time=20,930.586..246,594.382 rows=93,058 loops=1)

  • Filter: (date(time_iso) = '2018-11-01'::date)
  • Rows Removed by Filter: 1896023
12. 1,016.380 16,630.766 ↓ 1.0 389,078 1

Hash (cost=8,703.58..8,703.58 rows=380,958 width=53) (actual time=16,630.766..16,630.766 rows=389,078 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 9350kB
13. 15,614.386 15,614.386 ↓ 1.0 389,078 1

Seq Scan on maddr sender (cost=0.00..8,703.58 rows=380,958 width=53) (actual time=0.033..15,614.386 rows=389,078 loops=1)

14. 1,237.899 1,237.899 ↑ 1.0 1 95,223

Index Scan using maddr_pkey on maddr recip (cost=0.42..0.45 rows=1 width=53) (actual time=0.012..0.013 rows=1 loops=95,223)

  • Index Cond: (msgrcpt.rid = id)
15. 0.856 0.856 ↓ 0.0 0 856

Seq Scan on rbls (cost=0.00..14.60 rows=460 width=149) (actual time=0.001..0.001 rows=0 loops=856)

16. 7.704 7.704 ↑ 1.0 5 856

Seq Scan on viruses (cost=0.00..1.05 rows=5 width=31) (actual time=0.002..0.009 rows=5 loops=856)

17. 11.984 11.984 ↑ 1.0 9 856

Seq Scan on virus_scanners (cost=0.00..1.09 rows=9 width=6) (actual time=0.002..0.014 rows=9 loops=856)