View preformance oracle to postgresql - Mailing list pgsql-performance
| From | Reddygari, Pavan |
|---|---|
| Subject | View preformance oracle to postgresql |
| Date | |
| Msg-id | 9909AFED-314C-4AFE-AF7F-77645E7FAA2F@amazon.com Whole thread Raw |
| Responses |
Re: View preformance oracle to postgresql
(Laurenz Albe <laurenz.albe@cybertec.at>)
Re: View preformance oracle to postgresql (Merlin Moncure <mmoncure@gmail.com>) |
| List | pgsql-performance |
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.
pgsql-performance by date: