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

I will gather all information and get back to you

On Sat, Apr 3, 2021 at 9:00 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> so 3. 4. 2021 v 17:15 odesílatel aditya desai <admad123(at)gmail(dot)com> napsal:
>
>> Hi Pavel,
>> Thanks for response. Please see below.
>> work_mem=16MB
>> maintenance_work_mem=1GB
>> effective_cache_size=160GB
>> shared_buffers=64GB
>> force_parallel_mode=ON
>>
>
> force_parallel_mode is very bad idea. efective_cache_size=160GB can be too
> much too. work_mem 16 MB is maybe too low. The configuration looks a little
> bit chaotic :)
>
> How much has RAM your server? How much CPU cores are there? What is
> max_connections?
>
> Regards
>
> Pavel
>
>
>
>> Regards,
>> Aditya.
>>
>>
>> On Sat, Apr 3, 2021 at 7:38 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>>
>>>
>>> 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.
>>>>
>>>