SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

From: aditya desai <admad123(at)gmail(dot)com>
To: Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Date: 2021-04-03 13:38:22
Message-ID: CAN0SRDFV=Fv0zXHCGbh7gh=MTfw05Xd1x7gjJrZs5qn-TEphOw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hi,
We migrated our Oracle Databases to PostgreSQL. One of the simple select
query that takes 4 ms on Oracle is taking around 200 ms on PostgreSQL.
Could you please advise. Please find query and query plans below. Gather
cost seems high. Will increasing max_parallel_worker_per_gather help?

explain analyse SELECT bom.address_key dom2137,bom.address_type_key
dom1727,bom.start_date dom1077,bom.end_date dom828,bom.address_status_key
dom1955,bom.address_role_key dom1711,bom.delivery_point_created
dom2362,bom.postcode dom873,bom.postcode_name dom1390,bom.street_name
dom1186,bom.premises_number_1 dom1777,bom.premises_number_2
dom1778,bom.premises_letter_1 dom1784,bom.premises_letter_2
dom1785,bom.premises_separator dom1962,bom.stairway dom892,bom.po_box
dom653,bom.apartment_number dom1732,bom.apartment_letter
dom1739,bom.street_key dom1097,bom.address_use_key dom1609,bom.language_key
dom1272,bom.address_family_id dom1796,bom.cur_address_key
dom2566,bom.created_by dom1052,bom.modified_by dom1158,bom.creation_time
dom1392,bom.modification_time dom1813 FROM DEPT.address dom WHERE
address_key = 6113763

[
{
"Plan": {
"Node Type": "Gather",
"Parallel Aware": false,
"Actual Rows": 1,
"Actual Loops": 1,
"Workers Planned": 1,
"Workers Launched": 1,
"Single Copy": true,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "address1_i7",
"Relation Name": "address",
"Alias": "dom",
"Actual Rows": 1,
"Actual Loops": 1,
"Index Cond": "(address_key = 6113763)",
"Rows Removed by Index Recheck": 0
}
]
},
"Triggers": []
}
]

"Gather (cost=1000.43..1002.75 rows=1 width=127) (actual
time=174.318..198.539 rows=1 loops=1)"
" Workers Planned: 1"
" Workers Launched: 1"
" Single Copy: true"
" -> Index Scan using address1_i7 on address1 dom (cost=0.43..2.65 rows=1
width=127) (actual time=0.125..0.125 rows=1 loops=1)"
" Index Cond: (address_key = 6113763)"
"Planning Time: 0.221 ms"
"Execution Time: 198.601 ms"

Regards,
Aditya.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2021-04-03 13:39:55 Re: Additional Chapter for Tutorial - arch-dev.sgml
Previous Message Michael Paquier 2021-04-03 12:32:49 Re: fix old confusing JSON example

Browse pgsql-performance by date

  From Date Subject
Next Message Bharath Rupireddy 2021-04-03 13:47:47 Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Previous Message Adalberto Caccia 2021-04-01 09:23:53 Re: High availability management tool.