0050860: Query for retrieving the Discount Filter Characteristics take too long

Limit  (cost=1622.63..1633.23 rows=530 width=196) (actual time=161442.610..161443.361 rows=2185 loops=1)
   ->  Unique  (cost=1622.63..1633.23 rows=530 width=196) (actual time=161442.608..161443.234 rows=2185 loops=1)
         ->  Sort  (cost=1622.63..1623.96 rows=530 width=196) (actual time=161442.601..161442.698 rows=2185 loops=1)
               Sort Key: pricingadj0_.m_characteristic_id, (COALESCE(pricingadj0_.m_offer_characteristic_id, characteri2_.m_ch_value_id)), characteri2_.m_ch_value_id, pricingadj0_.m_offer_id, characteri1
_.name, (CASE WHEN ((pricingadj0_.isactive = 'Y'::bpchar) AND (pricingadj4_.isactive = 'Y'::bpchar)) THEN true ELSE false END), pricingadj0_.isincludecharacteristics
               Sort Method: quicksort  Memory: 677kB
               ->  Nested Loop Left Join  (cost=114.18..1598.65 rows=530 width=196) (actual time=119.769..161435.878 rows=2185 loops=1)
                     Filter: (m_isparent_ch_value(characteri2_.m_ch_value_id, pricingadj0_.m_ch_value_id, pricingadj0_.m_characteristic_id) <> '-1'::numeric)
                     Rows Removed by Filter: 3822758
                     ->  Nested Loop Left Join  (cost=113.89..1023.16 rows=1 width=183) (actual time=13.989..88.740 rows=2185 loops=1)
                           Join Filter: ((pricingadj0_.m_characteristic_id)::text = (characteri1_.m_characteristic_id)::text)
                           Rows Removed by Join Filter: 1681
                           ->  Nested Loop  (cost=113.89..1022.05 rows=1 width=138) (actual time=13.984..83.271 rows=2185 loops=1)
                                 ->  Hash Join  (cost=113.47..605.60 rows=23 width=171) (actual time=12.074..24.354 rows=2808 loops=1)
                                       Hash Cond: ((pricingadj0_.m_offer_id)::text = (pricingadj4_.m_offer_id)::text)
                                       ->  Seq Scan on m_offer_characteristic pricingadj0_  (cost=0.00..469.48 rows=8629 width=136) (actual time=0.012..5.318 rows=8518 loops=1)
                                             Filter: (isactive = 'Y'::bpchar)
                                             Rows Removed by Filter: 297
                                       ->  Hash  (cost=100.53..100.53 rows=1035 width=35) (actual time=12.049..12.050 rows=1522 loops=1)
                                             Buckets: 2048  Batches: 1  Memory Usage: 116kB
                                             ->  Index Scan using em_obpos_m_offer_active on m_offer pricingadj4_  (cost=0.42..100.53 rows=1035 width=35) (actual time=0.044..11.759 rows=1522 loops=1)
                                                   Index Cond: (isactive = 'Y'::bpchar)
                                 ->  Index Scan using m_offer_key on m_offer pricingadj6_  (cost=0.42..18.11 rows=1 width=33) (actual time=0.017..0.017 rows=1 loops=2808)
                                       Index Cond: ((m_offer_id)::text = (pricingadj0_.m_offer_id)::text)
                                       Filter: (((em_obdisc_c_currency_id IS NULL) OR ((em_obdisc_c_currency_id)::text = '102'::text)) AND ((ad_client_id)::text = '757D621ABD1948F5BCBAD91F19BB70AC'::text
) AND ((ad_org_id)::text = ANY ('{0,21953EE6A34A424DA97BA8863953A3BB,1F000EB998B94441AC9388472697307F,F57042F02962424BAD986D4F8588E199,AF8F3A088EC54473A02AD68E0556BBE3}'::text[])) AND ((((pricelist_selec
tion)::text = 'Y'::text) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR (((pricelist_selection)::text = 'N'::text) AND (alternatives: SubPlan 3 or hashed SubPlan 4))) AND ((((org_selection):
:text = 'Y'::text) AND (NOT (alternatives: SubPlan 7 or hashed SubPlan 8))) OR (((org_selection)::text = 'N'::text) AND (alternatives: SubPlan 9 or hashed SubPlan 10))) AND (((product_selection)::text = 
'Y'::text) OR (((product_selection)::text = 'N'::text) AND (alternatives: SubPlan 5 or hashed SubPlan 6))))
                                       Rows Removed by Filter: 0
                                       SubPlan 1
                                         ->  Seq Scan on m_offer_pricelist pricingadj7_  (cost=0.00..1.03 rows=1 width=0) (never executed)
                                               Filter: ((isactive = 'Y'::bpchar) AND ((m_offer_id)::text = (pricingadj6_.m_offer_id)::text) AND ((m_pricelist_id)::text = '638015D6C28C43CC82FDEEAC658A824C
'::text))
                                       SubPlan 2
                                         ->  Seq Scan on m_offer_pricelist pricingadj7__1  (cost=0.00..1.03 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)
                                               Filter: ((isactive = 'Y'::bpchar) AND ((m_pricelist_id)::text = '638015D6C28C43CC82FDEEAC658A824C'::text))
                                               Rows Removed by Filter: 2
                                       SubPlan 3
                                         ->  Seq Scan on m_offer_pricelist pricingadj8_  (cost=0.00..1.03 rows=1 width=0) (never executed)
                                               Filter: ((isactive = 'Y'::bpchar) AND ((m_offer_id)::text = (pricingadj6_.m_offer_id)::text) AND ((m_pricelist_id)::text = '638015D6C28C43CC82FDEEAC658A824C
'::text))
                                       SubPlan 4
                                         ->  Seq Scan on m_offer_pricelist pricingadj8__1  (cost=0.00..1.03 rows=1 width=32) (never executed)
                                               Filter: ((isactive = 'Y'::bpchar) AND ((m_pricelist_id)::text = '638015D6C28C43CC82FDEEAC658A824C'::text))
                                       SubPlan 7
                                         ->  Index Scan using m_offer_org_unique on m_offer_organization pricingadj12_  (cost=0.55..2.77 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=2183)
                                               Index Cond: (((ad_org_id)::text = '21953EE6A34A424DA97BA8863953A3BB'::text) AND ((m_offer_id)::text = (pricingadj6_.m_offer_id)::text))
                                               Filter: (isactive = 'Y'::bpchar)
                                       SubPlan 8
                                                                              SubPlan 9
                                         ->  Index Scan using m_offer_org_unique on m_offer_organization pricingadj13_  (cost=0.55..2.77 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=210)
                                               Index Cond: (((ad_org_id)::text = '21953EE6A34A424DA97BA8863953A3BB'::text) AND ((m_offer_id)::text = (pricingadj6_.m_offer_id)::text))
                                               Filter: (isactive = 'Y'::bpchar)
                                       SubPlan 10
                                         ->  Bitmap Heap Scan on m_offer_organization pricingadj13__1  (cost=16.14..656.90 rows=591 width=32) (never executed)
                                               Recheck Cond: ((ad_org_id)::text = '21953EE6A34A424DA97BA8863953A3BB'::text)
                                               Filter: (isactive = 'Y'::bpchar)
                                               ->  Bitmap Index Scan on m_offer_org_unique  (cost=0.00..15.99 rows=593 width=0) (never executed)
                                                     Index Cond: ((ad_org_id)::text = '21953EE6A34A424DA97BA8863953A3BB'::text)
                                       SubPlan 5
                                         ->  Nested Loop  (cost=1.52..266.46 rows=38 width=0) (never executed)
                                               Join Filter: ((pricingadj9_.m_product_id)::text = (obretco_pr10_.m_product_id)::text)
                                               ->  Nested Loop  (cost=0.97..227.51 rows=58 width=66) (never executed)
                                                     ->  Index Scan using m_offerproduct_offer_idx on m_offer_product pricingadj9_  (cost=0.41..66.56 rows=58 width=33) (never executed)
                                                           Index Cond: ((m_offer_id)::text = (pricingadj6_.m_offer_id)::text)
                                                           Filter: (isactive = 'Y'::bpchar)
                                                     ->  Index Scan using m_product_key on m_product product11_  (cost=0.56..2.78 rows=1 width=33) (never executed)
                                                           Index Cond: ((m_product_id)::text = (pricingadj9_.m_product_id)::text)
                                                           Filter: (isactive = 'Y'::bpchar)
                                               ->  Index Scan using obretco_prol_product_un on obretco_prol_product obretco_pr10_  (cost=0.56..0.66 rows=1 width=33) (never executed)
                                                     Index Cond: (((obretco_productlist_id)::text = '5BD0E488E2B44A6488E91BCA79447FFC'::text) AND ((m_product_id)::text = (product11_.m_product_id)::text))
                                                     Filter: (isactive = 'Y'::bpchar)
                                       SubPlan 6
                                         ->  Nested Loop  (cost=1.11..161754.49 rows=33150 width=32) (never executed)
                                               Join Filter: ((pricingadj9__1.m_product_id)::text = (obretco_pr10__1.m_product_id)::text)
                                               ->  Nested Loop  (cost=0.56..127966.64 rows=50316 width=99) (never executed)
                                                     ->  Seq Scan on m_offer_product pricingadj9__1  (cost=0.00..2612.24 rows=50316 width=66) (never executed)
                                                           Filter: (isactive = 'Y'::bpchar)
                                                     ->  Index Scan using m_product_key on m_product product11__1  (cost=0.56..2.49 rows=1 width=33) (never executed)
                                                           Index Cond: ((m_product_id)::text = (pricingadj9__1.m_product_id)::text)
                                                           Filter: (isactive = 'Y'::bpchar)
                                               ->  Index Scan using obretco_prol_product_un on obretco_prol_product obretco_pr10__1  (cost=0.56..0.66 rows=1 width=33) (never executed)
                                                     Index Cond: (((obretco_productlist_id)::text = '5BD0E488E2B44A6488E91BCA79447FFC'::text) AND ((m_product_id)::text = (product11__1.m_product_id)::text))
                                                     Filter: (isactive = 'Y'::bpchar)
                           ->  Seq Scan on m_characteristic characteri1_  (cost=0.00..1.05 rows=5 width=45) (actual time=0.000..0.000 rows=2 loops=2185)
                     ->  Index Scan using m_ch_value_characteristic on m_ch_value characteri2_  (cost=0.29..138.14 rows=1656 width=66) (actual time=0.006..0.508 rows=1751 loops=2185)
                           Index Cond: ((characteri1_.m_characteristic_id)::text = (m_characteristic_id)::text)
 Planning time: 7.175 ms
 Execution time: 161443.907 ms

SELECT
    DISTINCT COALESCE (pricingadj0_.M_Offer_Characteristic_ID,
        characteri2_.M_Ch_Value_ID) AS col_0_0_,
    pricingadj0_.M_Characteristic_ID AS col_1_0_,
    characteri2_.M_Ch_Value_ID AS col_2_0_,
    pricingadj0_.M_Offer_ID AS col_3_0_,
    characteri1_.Name AS col_4_0_,
    CASE
        WHEN pricingadj0_.Isactive = 'Y'
        AND pricingadj4_.IsActive = 'Y' THEN TRUE
        ELSE FALSE
    END AS col_5_0_,
    pricingadj0_.Isincludecharacteristics AS col_6_0_
FROM
    M_Offer_Characteristic pricingadj0_
    LEFT OUTER JOIN M_Characteristic characteri1_ ON pricingadj0_.M_Characteristic_ID = characteri1_.M_Characteristic_ID
    LEFT OUTER JOIN M_Ch_Value characteri2_ ON characteri1_.M_Characteristic_ID = characteri2_.M_Characteristic_ID
    CROSS JOIN M_Offer pricingadj4_
WHERE
    pricingadj0_.M_Offer_ID = pricingadj4_.M_Offer_ID
    AND pricingadj0_.Isactive = 'Y'
    AND pricingadj4_.IsActive = 'Y'
    AND m_isparent_ch_value (characteri2_.M_Ch_Value_ID, pricingadj0_.M_Ch_Value_ID, pricingadj0_.M_Characteristic_ID)
    <> - 1
    AND (EXISTS (
            SELECT
                1
            FROM
                M_Offer pricingadj6_
            WHERE
                pricingadj6_.AD_Client_ID = '757D621ABD1948F5BCBAD91F19BB70AC'
                AND (pricingadj6_.Pricelist_Selection = 'Y'
                    AND NOT (EXISTS (
                            SELECT
                                1
                            FROM
                                M_Offer_PriceList pricingadj7_
                            WHERE
                                pricingadj7_.IsActive = 'Y'
                                AND pricingadj7_.M_Offer_ID = pricingadj6_.M_Offer_ID
                                AND pricingadj7_.M_PriceList_ID = '638015D6C28C43CC82FDEEAC658A824C'))
                    OR pricingadj6_.Pricelist_Selection = 'N'
                    AND (EXISTS (
                            SELECT
                                1
                            FROM
                                M_Offer_PriceList pricingadj8_
                            WHERE
                                pricingadj8_.IsActive = 'Y'
                                AND pricingadj8_.M_Offer_ID = pricingadj6_.M_Offer_ID
                                AND pricingadj8_.M_PriceList_ID = '638015D6C28C43CC82FDEEAC658A824C')))
                AND (pricingadj6_.Product_Selection = 'Y'
                    OR pricingadj6_.Product_Selection = 'N'
                    AND (EXISTS (
                            SELECT
                                1
                            FROM
                                M_Offer_Product pricingadj9_
                            CROSS JOIN OBRETCO_Prol_Product obretco_pr10_
                        CROSS JOIN M_Product product11_
                    WHERE
                        pricingadj9_.M_Product_ID = product11_.M_Product_ID
                        AND pricingadj9_.M_Offer_ID = pricingadj6_.M_Offer_ID
                        AND pricingadj9_.IsActive = 'Y'
                        AND pricingadj9_.M_Product_ID = obretco_pr10_.M_Product_ID
                        AND obretco_pr10_.Isactive = 'Y'
                        AND product11_.IsActive = 'Y'
                        AND obretco_pr10_.Obretco_Productlist_ID = '5BD0E488E2B44A6488E91BCA79447FFC')))
                AND (pricingadj6_.AD_Org_ID IN ('0', '21953EE6A34A424DA97BA8863953A3BB', '1F000EB998B94441AC9388472697307F', 'F57042F02962424BAD986D4F8588E199', 'AF8F3A088EC54473A02AD68E0556BBE3'))
                AND (pricingadj6_.Org_Selection = 'Y'
                    AND NOT (EXISTS (
                            SELECT
                                1
                            FROM
                                M_Offer_Organization pricingadj12_
                            WHERE
                                pricingadj12_.Isactive = 'Y'
                                AND pricingadj12_.M_Offer_ID = pricingadj6_.M_Offer_ID
                                AND pricingadj12_.AD_Org_ID = '21953EE6A34A424DA97BA8863953A3BB'))
                    OR pricingadj6_.Org_Selection = 'N'
                    AND (EXISTS (
                            SELECT
                                1
                            FROM
                                M_Offer_Organization pricingadj13_
                            WHERE
                                pricingadj13_.Isactive = 'Y'
                                AND pricingadj13_.M_Offer_ID = pricingadj6_.M_Offer_ID
                                AND pricingadj13_.AD_Org_ID = '21953EE6A34A424DA97BA8863953A3BB')))
                AND (pricingadj6_.EM_OBDISC_C_Currency_ID IS NULL
                    OR pricingadj6_.EM_OBDISC_C_Currency_ID = '102')
                AND pricingadj0_.M_Offer_ID = pricingadj6_.M_Offer_ID))
    AND 1 = 1
ORDER BY
    pricingadj0_.M_Characteristic_ID ASC
LIMIT '50000';