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

so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123(at)gmail(dot)com> napsal:

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

You should have broken configuration - there is not any reason to start
parallelism - probably some option in postgresql.conf has very bad value.
Second - it's crazy to see 200 ms just on interprocess communication -
maybe your CPU is overutilized.

Regards

Pavel

>
>
> Regards,
> Aditya.
>