From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Reddygari, Pavan" <pkreddy(at)amazon(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: View preformance oracle to postgresql |
Date: | 2018-01-10 13:58:28 |
Message-ID: | CAHyXU0ys9ZDpv91e5jZ64Br1rztBdYsaGjZ5Z_2=Zx5MqEbxUg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2018-01-10 15:14:09 | RE: PG 9.5 2 tables same DDL with diff size |
Previous Message | Dinesh Chandra 12108 | 2018-01-10 12:42:24 | RE: Re: Unable to connect Postgres using psql while postgres is already running. |