Re: View preformance oracle to postgresql

[Merlin Moncure]

On Tue, Jan 9, 2018 at 3:32 PM, Reddygari, Pavan  wrote:
>
> A view got converted to postgresql, performance while querying the view in 
> postgresql is 10X longer compared to oracle.
>
>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;

can you try rewriting the (more sanely formatted)
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
  );

to
FROM item_attribute_value a
JOIN item_attribute ON a.iav_iat_id = item_attribute.iat_id
JOIN
(
   SELECT max(b.iav_version) AS iav_version
   FROM item_attribute_value b
   GROUP BY iav_itm_id, iav_iat_id
) q USING (iav_itm_id, iav_iat_id, iav_version);

merlin

Re: View preformance oracle to postgresql

[Laurenz Albe]

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