explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mf6O

Settings
# exclusive inclusive rows x rows loops node
1. 1,090.218 537,554.540 ↓ 343,135.0 343,135 1

HashAggregate (cost=5,762,660.58..5,762,660.59 rows=1 width=8) (actual time=537,428.157..537,554.540 rows=343,135 loops=1)

2. 1,733.028 536,464.322 ↓ 1,287,149.0 1,287,149 1

Nested Loop (cost=5,620,808.81..5,762,660.58 rows=1 width=8) (actual time=273,820.480..536,464.322 rows=1,287,149 loops=1)

  • Join Filter: (SubPlan 1)
3. 14,729.495 297,168.356 ↓ 2,647,500.0 2,647,500 1

Merge Join (cost=5,620,808.81..5,762,596.29 rows=1 width=42) (actual time=273,818.895..297,168.356 rows=2,647,500 loops=1)

  • Merge Cond: (((fl.emal_id)::text = (eml.emal_id)::text) AND ((fl.ctm_nbr)::text = (eml.ctm_nbr)::text))
4. 255,384.670 268,753.100 ↓ 1.0 18,193,396 1

Sort (cost=4,109,730.95..4,154,697.95 rows=17,986,802 width=34) (actual time=260,377.926..268,753.100 rows=18,193,396 loops=1)

  • Sort Key: fl.emal_id, fl.ctm_nbr
  • Sort Method: external merge Disk: 782520kB
5. 7,355.492 13,368.430 ↓ 1.0 18,193,396 1

Bitmap Heap Scan on pe2_fast_lookup fl (cost=439,205.13..1,942,282.15 rows=17,986,802 width=34) (actual time=6,547.266..13,368.430 rows=18,193,396 loops=1)

  • Recheck Cond: (orgid = 2)
6. 6,012.938 6,012.938 ↓ 1.0 18,264,820 1

Bitmap Index Scan on pe2_fast_lookup_orgid_contactid (cost=0.00..434,708.43 rows=17,986,802 width=0) (actual time=6,012.938..6,012.938 rows=18,264,820 loops=1)

  • Index Cond: (orgid = 2)
7. 6,948.265 13,685.761 ↓ 2.7 2,647,583 1

Sort (cost=1,510,726.54..1,513,145.26 rows=967,489 width=34) (actual time=12,991.362..13,685.761 rows=2,647,583 loops=1)

  • Sort Key: eml.emal_id, eml.ctm_nbr
  • Sort Method: quicksort Memory: 137194kB
8. 6,212.442 6,737.496 ↓ 1.2 1,126,925 1

Bitmap Heap Scan on lsteml_m eml (cost=33,291.79..1,414,539.33 rows=967,489 width=34) (actual time=796.318..6,737.496 rows=1,126,925 loops=1)

  • Recheck Cond: (((list_cde)::text = 'HOUSECAL'::text) AND ((eml_sta)::text = 'A'::text))
9. 525.054 525.054 ↓ 1.2 1,127,119 1

Bitmap Index Scan on lsteml_m_list_cde_emal_sta_dte_add (cost=0.00..33,049.92 rows=967,489 width=0) (actual time=525.054..525.054 rows=1,127,119 loops=1)

  • Index Cond: (((list_cde)::text = 'HOUSECAL'::text) AND ((eml_sta)::text = 'A'::text))
10. 31,770.000 31,770.000 ↑ 1.0 1 2,647,500

Index Scan using cdseml_m_new_pkey1 on cdseml_m cds (cost=0.00..9.09 rows=1 width=13) (actual time=0.012..0.012 rows=1 loops=2,647,500)

  • Index Cond: ((cds.emal_id)::text = (eml.emal_id)::text)
  • Filter: (cds.emal_vld <> 'N'::bpchar)
11.          

SubPlan (forNested Loop)

12. 2,638.371 205,792.938 ↓ 0.0 0 2,638,371

Limit (cost=41.42..55.18 rows=1 width=0) (actual time=0.078..0.078 rows=0 loops=2,638,371)

13. 19,628.136 203,154.567 ↓ 0.0 0 2,638,371

Nested Loop (cost=41.42..55.18 rows=1 width=0) (actual time=0.077..0.077 rows=0 loops=2,638,371)

  • Join Filter: ((((eml2.lst_src)::text !~~ 'X%'::text) AND ((('now'::text)::date - ($0)::date) < 270)) OR (((('now'::text)::date - ($0)::date) > 270) AND ((('now'::text)::date - (lo
14. 13,191.855 131,918.550 ↓ 2.0 2 2,638,371

Bitmap Heap Scan on lsteml_m eml2 (cost=41.42..43.44 rows=1 width=21) (actual time=0.049..0.050 rows=2 loops=2,638,371)

  • Recheck Cond: (((list_cde)::text = ANY ('{HOUSECAL,HOUSE3RD}'::text[])) AND ((emal_id)::text = ($1)::text))
  • Filter: ((eml_sta)::text = 'A'::text)
15. 118,726.695 118,726.695 ↓ 2.0 2 2,638,371

Bitmap Index Scan on lsteml_m_list_cde_emal_id_idx (cost=0.00..41.42 rows=1 width=0) (actual time=0.045..0.045 rows=2 loops=2,638,371)

  • Index Cond: (((list_cde)::text = ANY ('{HOUSECAL,HOUSE3RD}'::text[])) AND ((emal_id)::text = ($1)::text))
16. 51,607.881 51,607.881 ↑ 1.0 1 3,969,837

Index Scan using lastorgactions_unique on lastorgactions loa (cost=0.00..11.66 rows=1 width=21) (actual time=0.012..0.013 rows=1 loops=3,969,837)

  • Index Cond: (((loa.emal_id)::text = ($1)::text) AND (loa.orgid = 2))