Re: View preformance oracle to postgresql

[Merlin Moncure <mmoncure@xxxxxxxxx>]

On Tue, Jan 9, 2018 at 3:32 PM, Reddygari, Pavan <pkreddy@xxxxxxxxxx> 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