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