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