explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RtdG

Settings
# exclusive inclusive rows x rows loops node
1. 1,088.916 538,927.438 ↓ 343,178.0 343,178 1

HashAggregate (cost=6,112,351.14..6,112,351.15 rows=1 width=8) (actual time=538,814.312..538,927.438 rows=343,178 loops=1)

2. 3,361.813 537,838.522 ↓ 1,287,193.0 1,287,193 1

Nested Loop (cost=5,970,542.55..6,112,351.13 rows=1 width=8) (actual time=237,034.980..537,838.522 rows=1,287,193 loops=1)

  • Join Filter: (SubPlan 1)
3. 13,646.291 281,071.353 ↓ 2,647,627.0 2,647,627 1

Merge Join (cost=5,970,542.55..6,112,286.85 rows=1 width=42) (actual time=237,033.297..281,071.353 rows=2,647,627 loops=1)

  • Merge Cond: (((fl.emal_id)::text = (eml.emal_id)::text) AND ((fl.ctm_nbr)::text = (eml.ctm_nbr)::text))
4. 234,832.732 253,889.848 ↓ 1.0 18,192,721 1

Sort (cost=4,459,668.48..4,504,621.20 rows=17,981,088 width=34) (actual time=223,485.750..253,889.848 rows=18,192,721 loops=1)

  • Sort Key: fl.emal_id, fl.ctm_nbr
  • Sort Method: external merge Disk: 782472kB
5. 12,101.606 19,057.116 ↓ 1.0 18,192,721 1

Bitmap Heap Scan on pe2_fast_lookup fl (cost=439,154.84..1,941,754.44 rows=17,981,088 width=34) (actual time=7,522.175..19,057.116 rows=18,192,721 loops=1)

  • Recheck Cond: (orgid = 2)
6. 6,955.510 6,955.510 ↓ 1.0 18,258,336 1

Bitmap Index Scan on pe2_fast_lookup_orgid_contactid (cost=0.00..434,659.57 rows=17,981,088 width=0) (actual time=6,955.510..6,955.510 rows=18,258,336 loops=1)

  • Index Cond: (orgid = 2)
7. 6,557.924 13,535.214 ↓ 2.7 2,647,710 1

Sort (cost=1,510,522.86..1,512,941.25 rows=967,358 width=34) (actual time=12,874.864..13,535.214 rows=2,647,710 loops=1)

  • Sort Key: eml.emal_id, eml.ctm_nbr
  • Sort Method: quicksort Memory: 137199kB
8. 6,460.558 6,977.290 ↓ 1.2 1,127,001 1

Bitmap Heap Scan on lsteml_m eml (cost=33,288.44..1,414,349.62 rows=967,358 width=34) (actual time=775.199..6,977.290 rows=1,127,001 loops=1)

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

Bitmap Index Scan on lsteml_m_list_cde_emal_sta_dte_add (cost=0.00..33,046.60 rows=967,358 width=0) (actual time=516.732..516.732 rows=1,127,085 loops=1)

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

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

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

SubPlan (forNested Loop)

12. 0.000 221,633.832 ↓ 0.0 0 2,638,498

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

13. 20,960.468 221,633.832 ↓ 0.0 0 2,638,498

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

  • 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. 18,469.486 153,032.884 ↓ 2.0 2 2,638,498

Bitmap Heap Scan on lsteml_m eml2 (cost=41.42..43.43 rows=1 width=21) (actual time=0.055..0.058 rows=2 loops=2,638,498)

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

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

  • Index Cond: (((list_cde)::text = ANY ('{HOUSECAL,HOUSE3RD}'::text[])) AND ((emal_id)::text = ($1)::text))
16. 47,640.480 47,640.480 ↑ 1.0 1 3,970,040

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

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