Re: View preformance oracle to postgresql

On Tue, Jan 9, 2018 at 3:32 PM, Reddygari, Pavan <pkreddy(at)amazon(dot)com> 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