View preformance oracle to postgresql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 

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

| Id  | Operation              | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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.

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux