SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
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.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 7:08 PM aditya desai wrote: > > 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? No it doesn't. For small tables, parallelism might not help since it doesn't come for free. Try setting max_parallel_worker_per_gather to 0 i.e. without parallel query. With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
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 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.
>
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
It seems like something is missing. Is this table partitioned? How long ago was migration done? Has vacuum freeze and analyze of tables been done? Was index created after populating data or reindexed after perhaps? What version are you using?
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote: > so 3. 4. 2021 v 15:38 odesílatel aditya desai napsal: > > "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. It seems like force_parallel_mode is set, which is for debugging and not for "forcing things to go faster". Maybe we should rename the parameter, like parallel_mode_testing=on. http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html -- Justin
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Hi Michael, Thanks for your response. Is this table partitioned? - No How long ago was migration done? - 27th March 2021 Has vacuum freeze and analyze of tables been done? - We ran vacuum analyze. Was index created after populating data or reindexed after perhaps? - Index was created after data load and reindex was executed on all tables yesterday. Version is PostgreSQL-11 Regards, Aditya. On Sat, Apr 3, 2021 at 7:40 PM Michael Lewis wrote: > It seems like something is missing. Is this table partitioned? How long > ago was migration done? Has vacuum freeze and analyze of tables been done? > Was index created after populating data or reindexed after perhaps? What > version are you using? >
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 08:29:22PM +0530, aditya desai wrote:
> Hi Michael,
> Thanks for your response.
> Is this table partitioned? - No
> How long ago was migration done? - 27th March 2021
> Has vacuum freeze and analyze of tables been done? - We ran vacuum analyze.
> Was index created after populating data or reindexed after perhaps? - Index
> was created after data load and reindex was executed on all tables yesterday.
> Version is PostgreSQL-11
FYI, the output of these queries will show u what changes have been made
to the configuration file:
SELECT version();
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Hi Justin, Yes, force_parallel_mode is on. Should we set it off? Regards, Aditya. On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby wrote: > On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote: > > so 3. 4. 2021 v 15:38 odesílatel aditya desai > napsal: > > > "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. > > It seems like force_parallel_mode is set, which is for debugging and not > for > "forcing things to go faster". Maybe we should rename the parameter, like > parallel_mode_testing=on. > > http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html > > -- > Justin >
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > Hi Justin, > Yes, force_parallel_mode is on. Should we set it off? Yes. I bet someone set it without reading our docs: https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER --> Allows the use of parallel queries for testing purposes even in cases --> where no performance benefit is expected. We might need to clarify this sentence to be clearer it is _only_ for testing. Also, I suggest you review _all_ changes that have been made to the server since I am worried other unwise changes might also have been made. --- > > Regards, > Aditya. > > On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby wrote: > > On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote: > > so 3. 4. 2021 v 15:38 odesílatel aditya desai > napsal: > > > "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. > > It seems like force_parallel_mode is set, which is for debugging and not > for > "forcing things to go faster". Maybe we should rename the parameter, like > parallel_mode_testing=on. > > http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html > > -- > Justin > -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Thanks Bruce!! Will set it off and retry. On Sat, Apr 3, 2021 at 8:42 PM Bruce Momjian wrote: > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > > Hi Justin, > > Yes, force_parallel_mode is on. Should we set it off? > > Yes. I bet someone set it without reading our docs: > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > --> Allows the use of parallel queries for testing purposes even in > cases > --> where no performance benefit is expected. > > We might need to clarify this sentence to be clearer it is _only_ for > testing. Also, I suggest you review _all_ changes that have been made > to the server since I am worried other unwise changes might also have > been made. > > --- > > > > > Regards, > > Aditya. > > > > On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby > wrote: > > > > On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote: > > > so 3. 4. 2021 v 15:38 odesílatel aditya desai > > napsal: > > > > "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. > > > > It seems like force_parallel_mode is set, which is for debugging and > not > > for > > "forcing things to go faster". Maybe we should rename the > parameter, like > > parallel_mode_testing=on. > > > > > http://rhaas.blogspot.com/2018/06/using-forceparallelmode-correctly.html > > > > -- > > Justin > > > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > >
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 11:12:01AM -0400, Bruce Momjian wrote: > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > > Hi Justin, > > Yes, force_parallel_mode is on. Should we set it off? > > Yes. I bet someone set it without reading our docs: > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > --> Allows the use of parallel queries for testing purposes even in cases > --> where no performance benefit is expected. > > We might need to clarify this sentence to be clearer it is _only_ for > testing. Also, I suggest you review _all_ changes that have been made > to the server since I am worried other unwise changes might also have > been made. This brings up an issue we see occasionally. You can either leave everything as default, get advice on which defaults to change, or study each setting and then change defaults. Changing defaults without study often leads to poor configurations, as we are seeing here. The lucky thing is that you noticed a slow query and found the misconfiguration, but I am sure there are many servers where misconfiguration is never detected. I wish I knew how to improve this situation, but user education seems to be all we can do. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
adding the group. aad_log_min_messages | warning | configuration file application_name | psql | client archive_command | c:\postgres\bin\xlogcopy\xlogcopy.exe archive blob "%f" "%p" | configuration file archive_mode | on | configuration file archive_timeout | 15min | configuration file authentication_timeout| 30s | configuration file autovacuum_analyze_scale_factor | 0.05 | configuration file autovacuum_naptime| 15s | configuration file autovacuum_vacuum_scale_factor| 0.05 | configuration file bgwriter_delay| 20ms | configuration file bgwriter_flush_after | 512kB | configuration file bgwriter_lru_maxpages | 100 | configuration file checkpoint_completion_target | 0.9 | configuration file checkpoint_flush_after| 256kB | configuration file checkpoint_timeout| 5min | configuration file client_encoding | UTF8 | client connection_ID | 5b59f092-444c-49df-b5d6-a7a0028a7855 | client connection_PeerIP | fd40:4d4a:11:5067:6d11:500:a07:5144 | client connection_Vnet | on | client constraint_exclusion | partition | configuration file data_sync_retry | on | configuration file DateStyle | ISO, MDY | configuration file default_text_search_config| pg_catalog.english | configuration file dynamic_shared_memory_type| windows | configuration file effective_cache_size | 160GB | configuration file enable_seqscan| off | configuration file force_parallel_mode | off | configuration file from_collapse_limit | 15 | configuration file full_page_writes | off | configuration file hot_standby | on | configuration file hot_standby_feedback | on | configuration file join_collapse_limit | 15 | configuration file lc_messages | English_United States.1252 | configuration file lc_monetary | English_United States.1252 | configuration file lc_numeric| English_United States.1252 | configuration file lc_time | English_United States.1252 | configuration file listen_addresses | * | configuration file log_checkpoints | on | configuration file log_connections | on | configuration file log_destination | stderr | configuration file log_file_mode | 0640 | configuration file log_line_prefix | %t-%c- | configuration file log_min_messages_internal | info | configuration file log_rotation_age | 1h | configuration file log_rotation_size | 100MB | configuration file log_timezone | UTC | configuration file logging_collector | on | configuration file maintenance_work_mem | 1GB | configuration file max_connections | 1900 | configuration file max_parallel_workers_per_gather | 16 | configuration file max_replication_slots | 10 | configuration file max_stack_depth | 2MB | environment vari
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
wrote:
>
>
> so 3. 4. 2021 v 17:15 odesílatel aditya desai 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
>> wrote:
>>
>>>
>>>
>>> so 3. 4. 2021 v 15:38 odesílatel aditya desai
>>> 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.
>>>
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
so 3. 4. 2021 v 17:30 odesílatel aditya desai napsal: > adding the group. > > aad_log_min_messages | warning > | configuration file > application_name | psql >| client > archive_command | > c:\postgres\bin\xlogcopy\xlogcopy.exe archive blob "%f" "%p" | > configuration file > archive_mode | on >| configuration file > archive_timeout | 15min > | configuration file > authentication_timeout| 30s > | configuration file > autovacuum_analyze_scale_factor | 0.05 >| configuration file > autovacuum_naptime| 15s > | configuration file > autovacuum_vacuum_scale_factor| 0.05 >| configuration file > bgwriter_delay| 20ms >| configuration file > bgwriter_flush_after | 512kB > | configuration file > bgwriter_lru_maxpages | 100 > | configuration file > checkpoint_completion_target | 0.9 > | configuration file > checkpoint_flush_after| 256kB > | configuration file > checkpoint_timeout| 5min >| configuration file > client_encoding | UTF8 >| client > connection_ID | > 5b59f092-444c-49df-b5d6-a7a0028a7855 | client > connection_PeerIP | > fd40:4d4a:11:5067:6d11:500:a07:5144 | client > connection_Vnet | on >| client > constraint_exclusion | partition > | configuration file > data_sync_retry | on >| configuration file > DateStyle | ISO, MDY >| configuration file > default_text_search_config| pg_catalog.english >| configuration file > dynamic_shared_memory_type| windows > | configuration file > effective_cache_size | 160GB > | configuration file > enable_seqscan| off > | configuration file > force_parallel_mode | off > | configuration file > from_collapse_limit | 15 >| configuration file > full_page_writes | off > | configuration file > hot_standby | on >| configuration file > hot_standby_feedback | on >| configuration file > join_collapse_limit | 15 >| configuration file > lc_messages | English_United States.1252 >| configuration file > lc_monetary | English_United States.1252 >| configuration file > lc_numeric| English_United States.1252 >| configuration file > lc_time | English_United States.1252 >| configuration file > listen_addresses | * > | configuration file > log_checkpoints | on >| configuration file > log_connections | on >| configuration file > log_destination | stderr >| configuration file > log_file_mode | 0640 >| configuration file > log_line_prefix | %t-%c- >| configuration file > log_min_messages_internal | info >| configuration file > log_rotation_age | 1h >| configuration file > log_rotation_size | 100MB > | configuration file > log_timezone | UTC > | configuration file > logging_collector | on >| configuration file > maintenance_work_mem | 1GB > | configuration file > max_connections | 1900 >|
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 09:00:24PM +0530, aditya desai wrote: > adding the group. Perfect. That is a lot of non-default settings, so I would be concerned there are other misconfigurations in there --- the group here might have some tips. > aad_log_min_messages | warning > > | configuration file The above is not a PG config variable. > connection_ID | 5b59f092-444c-49df-b5d6-a7a0028a7855 > | client > connection_PeerIP | fd40:4d4a:11:5067:6d11:500:a07:5144 > > | client > connection_Vnet | on Uh, these are not a PG settings. You need to show us the output of version() because this is not standard Postgres. A quick search suggests this is a Microsoft version of Postgres. I will stop commenting. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Bruce Momjian writes: > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: >> Yes, force_parallel_mode is on. Should we set it off? > Yes. I bet someone set it without reading our docs: > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > --> Allows the use of parallel queries for testing purposes even in cases > --> where no performance benefit is expected. > We might need to clarify this sentence to be clearer it is _only_ for > testing. I wonder why it is listed under planner options at all, and not under developer options. regards, tom lane
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > >> Yes, force_parallel_mode is on. Should we set it off? > > > Yes. I bet someone set it without reading our docs: > > > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > --> Allows the use of parallel queries for testing purposes even in cases > > --> where no performance benefit is expected. > > > We might need to clarify this sentence to be clearer it is _only_ for > > testing. > > I wonder why it is listed under planner options at all, and not under > developer options. Because it's there to help DBAs catch errors in functions incorrectly marked as parallel safe. -- Justin
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 11:39:19AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > >> Yes, force_parallel_mode is on. Should we set it off? > > > Yes. I bet someone set it without reading our docs: > > > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > --> Allows the use of parallel queries for testing purposes even in cases > > --> where no performance benefit is expected. > > > We might need to clarify this sentence to be clearer it is _only_ for > > testing. > > I wonder why it is listed under planner options at all, and not under > developer options. I was kind of surprised by that myself since I was working on a blog entry about from_collapse_limit and join_collapse_limit. I think moving it makes sense. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
On Sat, Apr 3, 2021 at 10:41:14AM -0500, Justin Pryzby wrote: > On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > > >> Yes, force_parallel_mode is on. Should we set it off? > > > > > Yes. I bet someone set it without reading our docs: > > > > > > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > > > --> Allows the use of parallel queries for testing purposes even in > > > cases > > > --> where no performance benefit is expected. > > > > > We might need to clarify this sentence to be clearer it is _only_ for > > > testing. > > > > I wonder why it is listed under planner options at all, and not under > > developer options. > > Because it's there to help DBAs catch errors in functions incorrectly marked > as > parallel safe. Uh, isn't that developer/debugging? -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Thanks Justin. Will review all parameters and get back to you. On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby wrote: > On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > > >> Yes, force_parallel_mode is on. Should we set it off? > > > > > Yes. I bet someone set it without reading our docs: > > > > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > > > --> Allows the use of parallel queries for testing purposes even in > cases > > > --> where no performance benefit is expected. > > > > > We might need to clarify this sentence to be clearer it is _only_ for > > > testing. > > > > I wonder why it is listed under planner options at all, and not under > > developer options. > > Because it's there to help DBAs catch errors in functions incorrectly > marked as > parallel safe. > > -- > Justin >
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Hi Justin/Bruce/Pavel, Thanks for your inputs. After setting force_parallel_mode=off Execution time of same query was reduced to 1ms from 200 ms. Worked like a charm. We also increased work_mem to 80=MB. Thanks again. Regards, Aditya. On Sat, Apr 3, 2021 at 9:14 PM aditya desai wrote: > Thanks Justin. Will review all parameters and get back to you. > > On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby wrote: > >> On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: >> > Bruce Momjian writes: >> > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: >> > >> Yes, force_parallel_mode is on. Should we set it off? >> > >> > > Yes. I bet someone set it without reading our docs: >> > >> > > >> https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER >> > >> > > --> Allows the use of parallel queries for testing purposes even in >> cases >> > > --> where no performance benefit is expected. >> > >> > > We might need to clarify this sentence to be clearer it is _only_ for >> > > testing. >> > >> > I wonder why it is listed under planner options at all, and not under >> > developer options. >> >> Because it's there to help DBAs catch errors in functions incorrectly >> marked as >> parallel safe. >> >> -- >> Justin >> >
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
so 3. 4. 2021 v 19:37 odesílatel aditya desai napsal: > Hi Justin/Bruce/Pavel, > Thanks for your inputs. After setting force_parallel_mode=off Execution > time of same query was reduced to 1ms from 200 ms. Worked like a charm. We > also increased work_mem to 80=MB. Thanks > super. The too big max_connection can cause a lot of problems. You should install and use pgbouncer or pgpool II. https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/ Regards Pavel > again. > > Regards, > Aditya. > > On Sat, Apr 3, 2021 at 9:14 PM aditya desai wrote: > >> Thanks Justin. Will review all parameters and get back to you. >> >> On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby >> wrote: >> >>> On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: >>> > Bruce Momjian writes: >>> > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: >>> > >> Yes, force_parallel_mode is on. Should we set it off? >>> > >>> > > Yes. I bet someone set it without reading our docs: >>> > >>> > > >>> https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER >>> > >>> > > --> Allows the use of parallel queries for testing purposes even in >>> cases >>> > > --> where no performance benefit is expected. >>> > >>> > > We might need to clarify this sentence to be clearer it is _only_ for >>> > > testing. >>> > >>> > I wonder why it is listed under planner options at all, and not under >>> > developer options. >>> >>> Because it's there to help DBAs catch errors in functions incorrectly >>> marked as >>> parallel safe. >>> >>> -- >>> Justin >>> >>
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Yes. I have made suggestions on connection pooling as well. Currently it is being done from Application side. On Sat, Apr 3, 2021 at 11:12 PM Pavel Stehule wrote: > > > so 3. 4. 2021 v 19:37 odesílatel aditya desai napsal: > >> Hi Justin/Bruce/Pavel, >> Thanks for your inputs. After setting force_parallel_mode=off Execution >> time of same query was reduced to 1ms from 200 ms. Worked like a charm. We >> also increased work_mem to 80=MB. Thanks >> > > super. > > The too big max_connection can cause a lot of problems. You should install > and use pgbouncer or pgpool II. > > > https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/ > > Regards > > Pavel > > > > >> again. >> >> Regards, >> Aditya. >> >> On Sat, Apr 3, 2021 at 9:14 PM aditya desai wrote: >> >>> Thanks Justin. Will review all parameters and get back to you. >>> >>> On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby >>> wrote: >>> On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > >> Yes, force_parallel_mode is on. Should we set it off? > > > Yes. I bet someone set it without reading our docs: > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > --> Allows the use of parallel queries for testing purposes even in cases > > --> where no performance benefit is expected. > > > We might need to clarify this sentence to be clearer it is _only_ for > > testing. > > I wonder why it is listed under planner options at all, and not under > developer options. Because it's there to help DBAs catch errors in functions incorrectly marked as parallel safe. -- Justin >>>
Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
so 3. 4. 2021 v 19:45 odesílatel aditya desai napsal: > Yes. I have made suggestions on connection pooling as well. Currently it > is being done from Application side. > It is usual - but the application side pooling doesn't solve well overloading. The behaviour of the database is not linear. Usually opened connections are not active. But any non active connection can be changed to an active connection (there is not any limit for active connections), and then the performance can be very very slow. Good pooling and good setting of max_connections is protection against overloading. max_connection should be 10-20 x CPU cores (for OLTP) Regards Pavel > On Sat, Apr 3, 2021 at 11:12 PM Pavel Stehule > wrote: > >> >> >> so 3. 4. 2021 v 19:37 odesílatel aditya desai >> napsal: >> >>> Hi Justin/Bruce/Pavel, >>> Thanks for your inputs. After setting force_parallel_mode=off Execution >>> time of same query was reduced to 1ms from 200 ms. Worked like a charm. We >>> also increased work_mem to 80=MB. Thanks >>> >> >> super. >> >> The too big max_connection can cause a lot of problems. You should >> install and use pgbouncer or pgpool II. >> >> >> https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/ >> >> Regards >> >> Pavel >> >> >> >> >>> again. >>> >>> Regards, >>> Aditya. >>> >>> On Sat, Apr 3, 2021 at 9:14 PM aditya desai wrote: >>> Thanks Justin. Will review all parameters and get back to you. On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby wrote: > On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote: > > >> Yes, force_parallel_mode is on. Should we set it off? > > > > > Yes. I bet someone set it without reading our docs: > > > > > > https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER > > > > > --> Allows the use of parallel queries for testing purposes even > in cases > > > --> where no performance benefit is expected. > > > > > We might need to clarify this sentence to be clearer it is _only_ > for > > > testing. > > > > I wonder why it is listed under planner options at all, and not under > > developer options. > > Because it's there to help DBAs catch errors in functions incorrectly > marked as > parallel safe. > > -- > Justin >
