explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8GJy

Settings
# exclusive inclusive rows x rows loops node
1. 9.355 2,874,859.128 ↑ 74.5 98,468 1

Unique (cost=1,731,080,499.44..1,731,135,535.72 rows=7,338,171 width=44) (actual time=2,874,843.813..2,874,859.128 rows=98,468 loops=1)

2. 150.854 2,874,849.773 ↑ 74.5 98,468 1

Sort (cost=1,731,080,499.44..1,731,098,844.86 rows=7,338,171 width=44) (actual time=2,874,843.812..2,874,849.773 rows=98,468 loops=1)

  • Sort Key: messages_2019.arrival_date DESC, messages_2019.idnumber DESC
  • Sort Method: quicksort Memory: 10765kB
3. 163.947 2,874,698.919 ↑ 74.5 98,468 1

Nested Loop Anti Join (cost=1.01..1,730,114,699.51 rows=7,338,171 width=44) (actual time=24.242..2,874,698.919 rows=98,468 loops=1)

4. 232,917.403 2,873,823.450 ↑ 75.7 101,646 1

Index Scan using messages_2019_folder_arrival_date_key on messages_2019 (cost=0.57..1,719,582,840.47 rows=7,694,890 width=12) (actual time=24.220..2,873,823.450 rows=101,646 loops=1)

  • Index Cond: (((folder)::text = 'email'::text) AND (arrival_date >= '2019-12-01 00:00:00+00'::timestamp with time zone) AND (arrival_date < ('2019-12-31 00:00:00+00'::timestamp with time zone + '24:00:00'::interval)))
  • Filter: ((is_spam IS FALSE) AND (SubPlan 1))
  • Rows Removed by Filter: 15525017
5.          

SubPlan (for Index Scan)

6. 15,626.663 2,640,906.047 ↓ 0.0 0 15,626,663

Unique (cost=0.71..221.30 rows=3 width=4) (actual time=0.169..0.169 rows=0 loops=15,626,663)

7. 55,081.771 2,625,279.384 ↓ 0.0 0 15,626,663

Nested Loop (cost=0.71..221.30 rows=3 width=4) (actual time=0.168..0.168 rows=0 loops=15,626,663)

8. 78,133.315 78,133.315 ↑ 1.0 80 15,626,663

Seq Scan on acls_addresses_temp_aft_t at (cost=0.00..1.80 rows=80 width=30) (actual time=0.001..0.005 rows=80 loops=15,626,663)

9. 2,492,064.298 2,492,064.298 ↓ 0.0 0 1,246,032,149

Index Scan using messagecontacts_idnumber_lower_address_idx on messagecontacts mft (cost=0.71..2.73 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=1,246,032,149)

  • Index Cond: ((idnumber = messages_2019.idnumber) AND (lower(address) = at.address))
  • Filter: (messagefield = ANY ('{to,cc,bcc,from,bfrom}'::text[]))
10. 711.522 711.522 ↓ 0.0 0 101,646

Index Only Scan using flags_idnumber_flag_uniq on flags (cost=0.44..1.37 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=101,646)

  • Index Cond: (idnumber = messages_2019.idnumber)
  • Filter: (flag = ANY ('{10,11,12,13,15,20,21,30,41,43,45}'::integer[]))
  • Heap Fetches: 3179