pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedView preformance oracle to postgresql
3+ messages / 3 participants
[nested] [flat]
* View preformance oracle to postgresql
@ 2018-01-09 21:32 Reddygari, Pavan <pkreddy@amazon.com>
0 siblings, 2 replies; 3+ messages in thread
From: Reddygari, Pavan @ 2018-01-09 21:32 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: View preformance oracle to postgresql
@ 2018-01-10 10:41 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Reddygari, Pavan <pkreddy@amazon.com>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2018-01-10 10:41 UTC (permalink / raw)
To: Reddygari, Pavan <pkreddy@amazon.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: View preformance oracle to postgresql
@ 2018-01-10 13:58 Merlin Moncure <mmoncure@gmail.com>
parent: Reddygari, Pavan <pkreddy@amazon.com>
1 sibling, 0 replies; 3+ messages in thread
From: Merlin Moncure @ 2018-01-10 13:58 UTC (permalink / raw)
To: Reddygari, Pavan <pkreddy@amazon.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-01-10 13:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-09 21:32 View preformance oracle to postgresql Reddygari, Pavan <pkreddy@amazon.com>
2018-01-10 10:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-01-10 13:58 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox