Home > mailing lists

Thread: View preformance oracle to postgresql

View preformance oracle to postgresql

From

"Reddygari, Pavan"

Date:

10 January 2018, 03:32:33

Hi,

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.

Oracle version - Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production (RHEL7)

Postgresql database version - PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit (Amazon RDS)

Following details from oracle database.

SQL> set autot traceonly exp stat

SQL> SELECT IAT_ID, IAT_NAME, IAT_TYPE, IAV_VALUE, IAV_APPROVED FROM V_ITEM_ATTRIBUTEs WHERE IAV_ITM_ID = 2904107;

66 rows selected.

Elapsed: 00:00:00.02

Execution Plan

----------------------------------------------------------

Plan hash value: 1137648293

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 107 | 8 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 107 | 8 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 107 | 8 (0)| 00:00:01 |

| 3 | NESTED LOOPS | | 1 | 77 | 7 (0)| 00:00:01 |

| 4 | VIEW | VW_SQ_1 | 1 | 39 | 4 (0)| 00:00:01 |

| 5 | HASH GROUP BY | | 1 | 14 | 4 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | UNIQUE_IAV_VERSION | 23 | 322 | 4 (0)| 00:00:01 |

| 7 | TABLE ACCESS BY INDEX ROWID| ITEM_ATTRIBUTE_VALUE | 1 | 38 | 3 (0)| 00:00:01 |

|* 8 | INDEX UNIQUE SCAN | UNIQUE_IAV_VERSION | 1 | | 2 (0)| 00:00:01 |

|* 9 | INDEX UNIQUE SCAN | PK_IAT_ID | 1 | | 0 (0)| 00:00:01 |

| 10 | TABLE ACCESS BY INDEX ROWID | ITEM_ATTRIBUTE | 1 | 30 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("B"."IAV_ITM_ID"=2904107)

8 - access("A"."IAV_ITM_ID"=2904107 AND "ITEM_2"="A"."IAV_IAT_ID" AND

"A"."IAV_VERSION"="MAX(B.IAV_VERSION)")

9 - access("A"."IAV_IAT_ID"="IAT_ID")

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

10047 consistent gets

0 physical reads

0 redo size

4346 bytes sent via SQL*Net to client

568 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

66 rows processed

SQL execution details on Postgredql Database.

qpsnap1pg=> explain (analyze on, buffers on, timing on) SELECT IAT_ID, IAT_NAME, IAT_TYPE, IAV_VALUE, IAV_APPROVED FROM V_ITEM_ATTRIBUTEs WHERE IAV_ITM_ID = 2904107;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Nested Loop (cost=0.84..1282.74 rows=3 width=53) (actual time=0.904..464.233 rows=66 loops=1)

Buffers: shared hit=65460

-> Index Scan using idx_iav_itm_id on item_attribute_value a (cost=0.57..1275.83 rows=3 width=29) (actual time=0.895..463.787 rows=66 loops=1)

Index Cond: (iav_itm_id = '2904107'::numeric)

Filter: (iav_version = (SubPlan 2))

Rows Removed by Filter: 11931

Buffers: shared hit=65261

SubPlan 2

-> Result (cost=1.87..1.88 rows=1 width=32) (actual time=0.036..0.036 rows=1 loops=11997)

Buffers: shared hit=59985

InitPlan 1 (returns $2)

-> Limit (cost=0.57..1.87 rows=1 width=5) (actual time=0.034..0.034 rows=1 loops=11997)

Buffers: shared hit=59985

-> Index Only Scan Backward using unique_iav_version on item_attribute_value b (cost=0.57..3.17 rows=2 width=5) (actual time=0.032..0.032 rows=1 loops=11997)

Index Cond: ((iav_itm_id = a.iav_itm_id) AND (iav_iat_id = a.iav_iat_id) AND (iav_version IS NOT NULL))

Heap Fetches: 11997

Buffers: shared hit=59985

-> Index Scan using pk_iat_id on item_attribute (cost=0.28..2.29 rows=1 width=29) (actual time=0.003..0.004 rows=1 loops=66)

Index Cond: (iat_id = a.iav_iat_id)

Buffers: shared hit=199

Planning time: 0.554 ms

Execution time: 464.439 ms

(22 rows)

Time: 1616.691 ms

qpsnap1pg=>

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.

Thanks,

Pavan.

Re: View preformance oracle to postgresql

From

Laurenz Albe

Date:

10 January 2018, 16:41:04

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

Re: View preformance oracle to postgresql

From

Merlin Moncure

Date:

10 January 2018, 19:58:28

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