explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rfvt

Settings
# exclusive inclusive rows x rows loops node
1. 12.098 2,114,338.780 ↑ 1.9 98,466 1

Unique (cost=162,078,927.08..162,080,352.31 rows=190,031 width=44) (actual time=2,114,318.223..2,114,338.780 rows=98,466 loops=1)

2. 58.565 2,114,326.682 ↑ 1.5 126,663 1

Sort (cost=162,078,927.08..162,079,402.16 rows=190,031 width=44) (actual time=2,114,318.222..2,114,326.682 rows=126,663 loops=1)

  • Sort Key: messages_2019.arrival_date DESC, messages_2019.idnumber DESC
  • Sort Method: quicksort Memory: 12968kB
3. 67.205 2,114,268.117 ↑ 1.5 126,663 1

Nested Loop Anti Join (cost=4,695,517.65..162,062,265.28 rows=190,031 width=44) (actual time=1,912,121.273..2,114,268.117 rows=126,663 loops=1)

4. 4,519.974 2,113,937.598 ↑ 1.5 131,657 1

Hash Join (cost=4,695,517.22..161,723,352.19 rows=199,273 width=12) (actual time=1,912,121.222..2,113,937.598 rows=131,657 loops=1)

  • Hash Cond: (mft.idnumber = messages_2019.idnumber)
5. 832,367.026 2,021,610.388 ↓ 1.2 4,463,344 1

Hash Join (cost=2.80..156,843,700.83 rows=3,681,610 width=4) (actual time=2,944.960..2,021,610.388 rows=4,463,344 loops=1)

  • Hash Cond: (lower(mft.address) = at.address)
6. 1,189,243.332 1,189,243.332 ↓ 1.1 3,218,083,212 1

Seq Scan on messagecontacts mft (cost=0.00..142,393,839.51 rows=2,882,608,483 width=28) (actual time=0.242..1,189,243.332 rows=3,218,083,212 loops=1)

  • Filter: (messagefield = ANY ('{to,cc,bcc,from,bfrom}'::text[]))
  • Rows Removed by Filter: 2819584771
7. 0.007 0.030 ↑ 1.0 80 1

Hash (cost=1.80..1.80 rows=80 width=30) (actual time=0.030..0.030 rows=80 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
8. 0.023 0.023 ↑ 1.0 80 1

Seq Scan on acls_addresses_temp_aft_t at (cost=0.00..1.80 rows=80 width=30) (actual time=0.008..0.023 rows=80 loops=1)

9. 3,445.687 87,807.236 ↓ 1.0 15,626,663 1

Hash (cost=4,427,996.17..4,427,996.17 rows=15,389,780 width=12) (actual time=87,807.236..87,807.236 rows=15,626,663 loops=1)

  • Buckets: 2097152 Batches: 16 Memory Usage: 62302kB
10. 84,361.549 84,361.549 ↓ 1.0 15,626,663 1

Index Scan using messages_2019_arrival_date_key on messages_2019 (cost=0.57..4,427,996.17 rows=15,389,780 width=12) (actual time=0.586..84,361.549 rows=15,626,663 loops=1)

  • Index Cond: ((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 ((folder)::text = 'email'::text))
  • Rows Removed by Filter: 11037594
11. 263.314 263.314 ↓ 0.0 0 131,657

Index Only Scan using flags_idnumber_flag_uniq on flags (cost=0.44..1.70 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=131,657)

  • Index Cond: (idnumber = messages_2019.idnumber)
  • Filter: (flag = ANY ('{10,11,12,13,15,20,21,30,41,43,45}'::integer[]))
  • Heap Fetches: 4994
Planning time : 9.188 ms
Execution time : 2,114,344.220 ms