QUERY PLAN Sort (cost=104034.78..104034.78 rows=1 width=217) (actual time=11431.083..11431.089 rows=4 loops=1) Sort Key: product.prod_code -> GroupAggregate (cost=104034.72..104034.77 rows=1 width=217) (actual time=11431.021..11431.044 rows=4 loops=1) -> Sort (cost=104034.72..104034.72 rows=1 width=217) (actual time=11430.976..11430.981 rows=4 loops=1) Sort Key: product.prod_key, product_prepay.prepay, product."comment", product_general."comment", product.prod_code, product.guideline, product.lien_pri, product_condition.front_ratio_pct, product_condition.back_ratio_pct, product_condition.chap_7_bankr_discharge_mo, product_condition.chap_13_bankr_discharge_mo, product_condition.fclose_discharge_mo -> Nested Loop (cost=0.00..104034.71 rows=1 width=217) (actual time=6719.403..11430.930 rows=4 loops=1) -> Nested Loop (cost=0.00..104031.67 rows=1 width=225) (actual time=6719.383..11430.856 rows=4 loops=1) -> Nested Loop (cost=0.00..104028.64 rows=1 width=233) (actual time=6719.327..11430.695 rows=4 loops=1) -> Nested Loop (cost=0.00..104020.28 rows=1 width=241) (actual time=6719.252..11430.561 rows=4 loops=1) -> Merge Join (cost=0.00..103983.12 rows=11 width=128) (actual time=6719.148..11430.385 rows=4 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) -> Merge Left Join (cost=0.00..99852.29 rows=69650 width=89) (actual time=0.724..11376.116 rows=2443 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) Filter: ((("outer".state_apl)::text = 'NO'::text) OR ((("outer".state_apl)::text = 'YES'::text) AND (("inner".state)::text = 'NV'::text))) -> Merge Left Join (cost=0.00..26472.83 rows=5878 width=95) (actual time=0.484..2634.270 rows=2770 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) Filter: ((("outer".prop_type_apl)::text = 'NO'::text) OR ((("outer".prop_type_apl)::text = 'YES'::text) AND (("inner".prop_type)::text = 'DETACH'::text))) -> Merge Left Join (cost=0.00..15298.89 rows=3041 width=101) (actual time=0.408..1394.324 rows=3711 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) Filter: ((("outer".doc_level_apl)::text = 'NO'::text) OR ((("outer".doc_level_apl)::text = 'YES'::text) AND (("inner".doc_level)::text = 'AF'::text))) -> Merge Left Join (cost=0.00..13822.28 rows=3041 width=107) (actual time=0.341..1198.880 rows=5639 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) Filter: ((("outer".loan_purp_apl)::text = 'NO'::text) OR ((("outer".loan_purp_apl)::text = 'YES'::text) AND (("inner".loan_purp)::text = 'P'::text))) -> Merge Left Join (cost=0.00..11473.97 rows=3041 width=113) (actual time=0.261..891.512 rows=7587 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) Filter: ((("outer".prepay_apl)::text = 'NO'::text) OR (("inner".prepay)::text = 'P0'::text)) -> Merge Left Join (cost=0.00..8179.19 rows=3041 width=113) (actual time=0.191..459.599 rows=10103 loops=1) Merge Cond: ("outer".prod_general_key = "inner".prod_general_key) Filter: ((("outer".prop_use_apl)::text = 'NO'::text) OR ((("outer".prop_use_apl)::text = 'YES'::text) AND (("inner".prop_use)::text = 'PRIM'::text))) -> Index Scan using product_general_pkey on product_general (cost=0.00..6726.08 rows=3041 width=119) (actual time=0.123..231.697 rows=15535 loops=1) Filter: ((("disable")::text = 'E'::text) AND (((loan_amt_from = 0::numeric) AND (loan_amt_to = 0::numeric)) OR ((loan_amt_from IS NULL) AND (loan_amt_to IS NULL)) OR ((loan_amt_to >= 400000::numeric) AND (loan_amt_from <= 400000::numeric))) AND (((ltv_from_pct = 0::numeric) AND (ltv_to_pct = 0::numeric)) OR ((ltv_from_pct IS NULL) AND (ltv_to_pct IS NULL)) OR ((ltv_to_pct >= 80.000) AND (ltv_from_pct <= 80.000))) AND (((cltv_from_pct = 0::numeric) AND (cltv_to_pct = 0::numeric)) OR ((cltv_from_pct IS NULL) AND (cltv_to_pct IS NULL)) OR ((cltv_to_pct >= 80.000) AND (cltv_from_pct <= 80.000))) AND (((crd_scr_from = 0) AND (crd_scr_to = 0)) OR ((crd_scr_from IS NULL) AND (crd_scr_to IS NULL)) OR ((crd_scr_to >= 800) AND (crd_scr_from <= 800))) AND (((impnd)::text = 'NA'::text) OR ((impnd)::text = 'BOTH'::text) OR ((impnd)::text = 'YES'::text)) AND (((mort_ins)::text = 'NA'::text) OR ((mort_ins)::text = 'BOTH'::text) OR ((mort_ins)::text = 'NO'::text)) AND (((frgn_natl)::text = 'NA'::text) OR ((frgn_natl)::text = 'BOTH'::text) OR ((frgn_natl)::text = 'NO'::text))) -> Index Scan using product_property_use_pkey on product_property_use (cost=0.00..1308.44 rows=40404 width=16) (actual time=0.056..91.965 rows=40405 loops=1) -> Index Scan using product_prepay_pkey on product_prepay (cost=0.00..2976.11 rows=95288 width=14) (actual time=0.062..210.527 rows=95288 loops=1) -> Index Scan using product_loan_purpose_pkey on product_loan_purpose (cost=0.00..2112.61 rows=67243 width=15) (actual time=0.063..149.291 rows=67240 loops=1) -> Index Scan using product_doc_level_pkey on product_doc_level (cost=0.00..1325.69 rows=42249 width=14) (actual time=0.055..94.493 rows=42249 loops=1) -> Index Scan using product_property_type_pkey on product_property_type (cost=0.00..10145.64 rows=300912 width=17) (actual time=0.063..659.075 rows=300912 loops=1) -> Index Scan using product_state_pkey on product_state (cost=0.00..64240.87 rows=2159903 width=14) (actual time=0.074..4651.861 rows=2159466 loops=1) -> Index Scan using product_condition_i2 on product_condition (cost=0.00..3481.60 rows=9 width=55) (actual time=13.411..47.081 rows=1275 loops=1) Filter: (((exception)::text = 'YES'::text) AND (((total_lien_min_amt = 0::numeric) AND (total_lien_max_amt = 0::numeric)) OR ((total_lien_min_amt IS NULL) AND (total_lien_max_amt IS NULL)) OR ((total_lien_max_amt >= 400000::numeric) AND (total_lien_min_amt <= 400000::numeric))) AND (((secondary_finance)::text = 'NO'::text) OR ((secondary_finance)::text = 'YES'::text)) AND ((front_ratio_pct = 0::numeric) OR (front_ratio_pct IS NULL) OR (front_ratio_pct < 0.000)) AND ((back_ratio_pct = 0::numeric) OR (back_ratio_pct IS NULL) OR (back_ratio_pct < 10.000))) -> Index Scan using product_pkey on product (cost=0.00..3.37 rows=1 width=113) (actual time=0.030..0.033 rows=1 loops=4) Index Cond: (product.prod_key = "outer".prod_key) Filter: ((("disable")::text = 'E'::text) AND ((lien_pri)::text = '1'::text) AND ((int_only)::text = 'NO'::text) AND ((prod_cat)::text = '14'::text)) -> Index Scan using product_lock_term_i0 on product_lock_term (cost=0.00..8.35 rows=1 width=8) (actual time=0.022..0.025 rows=1 loops=4) Index Cond: ("outer".prod_key = product_lock_term.prod_key) Filter: (((lock_term)::text = 'B30'::text) AND (("disable")::text = 'E'::text)) -> Index Scan using product_mortgage_late_pkey on product_mortgage_late mortgagelate12 (cost=0.00..3.02 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=4) Index Cond: ("outer".mort_late_12_key = mortgagelate12.prod_mort_late_key) Filter: ((mort_late_30 >= 0) AND (mort_late_60 >= 0) AND (mort_late_90 >= 0)) -> Index Scan using product_mortgage_late_pkey on product_mortgage_late mortgagelate0 (cost=0.00..3.02 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=4) Index Cond: ("outer".mort_late_key = mortgagelate0.prod_mort_late_key) Filter: ((mort_late_30 >= 0) AND (mort_late_60 >= 0) AND (mort_late_90 >= 0)) Total runtime: 11431.640 ms