Re: View preformance oracle to postgresql

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