Pavan Reddygari wrote: > A view got converted to postgresql, performance while querying the view in postgresql is 10X longer compared to oracle. > Hardware resources are matching between oracle and postgresql. > > V_item_attributes view code as below, same in oracle and postgresql. > ------------------------------------------------------------------------------------- > SELECT a.iav_id, > a.iav_itm_id, > a.iav_iat_id, > a.iav_value, > a.iav_version, > a.iav_approved, > a.iav_create_date, > a.iav_created_by, > a.iav_modify_date, > a.iav_modified_by, > item_attribute.iat_id, > item_attribute.iat_name, > item_attribute.iat_type, > item_attribute.iat_status, > item_attribute.iat_requires_approval, > item_attribute.iat_multi_valued, > item_attribute.iat_inheritable, > item_attribute.iat_create_date, > item_attribute.iat_created_by, > item_attribute.iat_modify_date, > item_attribute.iat_modified_by, > item_attribute.iat_translated > FROM (item_attribute_value a > JOIN item_attribute ON ((a.iav_iat_id = item_attribute.iat_id))) > WHERE (a.iav_version = ( SELECT max(b.iav_version) AS max > FROM item_attribute_value b > WHERE ((b.iav_itm_id = a.iav_itm_id) AND (b.iav_iat_id = a.iav_iat_id)))); > > > Oracle is using push predicate of IAV_ITM_ID column wherever item_attribute_values table being used. > Any alternatives available to reduce view execution time in postgresql database or any hints, thoughts would be appreciated. If (iav_version, iav_itm_id, iav_iat_id) is unique, you could use SELECT DISTINCT ON (a.iav_itm_id, a.iav_iat_id) ... FROM item_attribute_value a JOIN item_attribute b ON ... ORDER BY a.iav_version DESC; Yours, Laurenz Albe
