From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Reddygari, Pavan <pkreddy@amazon.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: View preformance oracle to postgresql
Date: Wed, 10 Jan 2018 11:41:04 +0100
Message-ID: <1515580864.2848.33.camel@cybertec.at> (raw)
In-Reply-To: <9909AFED-314C-4AFE-AF7F-77645E7FAA2F@amazon.com>
References: <9909AFED-314C-4AFE-AF7F-77645E7FAA2F@amazon.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, pkreddy@amazon.com
Subject: Re: View preformance oracle to postgresql
In-Reply-To: <1515580864.2848.33.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox