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