Message-ID: <1515580864.2848.33.camel@cybertec.at>
Subject: Re: View preformance oracle to postgresql
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Reddygari, Pavan" <pkreddy@amazon.com>,
	"pgsql-performance@postgresql.org"
	 <pgsql-performance@postgresql.org>
Date: Wed, 10 Jan 2018 11:41:04 +0100
In-Reply-To: <9909AFED-314C-4AFE-AF7F-77645E7FAA2F@amazon.com>
References: <9909AFED-314C-4AFE-AF7F-77645E7FAA2F@amazon.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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