so 3. 4. 2021 v 15:38 odesílatel aditya desai <admad123@xxxxxxxxx> 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.
