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 <admad123(at)gmail(dot)com> 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
> | configuration file
> max_parallel_workers_per_gather | 16
> | configuration file
> max_replication_slots | 10
> | configuration file
> max_stack_depth | 2MB
> | environment variable
> max_wal_senders | 10
> | configuration file
> max_wal_size | 26931MB
> | configuration file
> min_wal_size | 4GB
> | configuration file
> pg_qs.query_capture_mode | top
> | configuration file
> pgms_wait_sampling.query_capture_mode | all
> | configuration file
> pgstat_udp_port | 20224
> | command line
> port | 20224
> | command line
> random_page_cost | 1.1
> | configuration file
> shared_buffers | 64GB
> | configuration file
> ssl | on
> | configuration file
> ssl_ca_file | root.crt
> | configuration file
> superuser_reserved_connections | 5
> | configuration file
> TimeZone | EET
> | configuration file
> track_io_timing | on
> | configuration file
> wal_buffers | 128MB
> | configuration file
> wal_keep_segments | 25
> | configuration file
> wal_level | replica
> | configuration file
> work_mem | 16MB
> | configuration file
>
>
max_connections | 1900

it is really not good - there can be very high CPU overloading with a lot
of others issues.

> On Sat, Apr 3, 2021 at 8:59 PM aditya desai <admad123(at)gmail(dot)com> wrote:
>
>> Hi Bruce,
>> Please find the below output.force_parallel_mode if off now.
>>
>> 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 variable
>> max_wal_senders | 10
>> | configuration file
>> max_wal_size | 26931MB
>> | configuration file
>> min_wal_size | 4GB
>> | configuration file
>> pg_qs.query_capture_mode | top
>> | configuration file
>> pgms_wait_sampling.query_capture_mode | all
>> | configuration file
>> pgstat_udp_port | 20224
>> | command line
>> port | 20224
>> | command line
>> random_page_cost | 1.1
>> | configuration file
>> shared_buffers | 64GB
>> | configuration file
>> ssl | on
>> | configuration file
>> ssl_ca_file | root.crt
>> | configuration file
>> superuser_reserved_connections | 5
>> | configuration file
>> TimeZone | EET
>> | configuration file
>> track_io_timing | on
>> | configuration file
>> wal_buffers | 128MB
>> | configuration file
>> wal_keep_segments | 25
>> | configuration file
>> wal_level | replica
>> | configuration file
>> work_mem | 16MB
>> | configuration file
>>
>>
>> Regards,
>> Aditya.
>>
>>
>>
>> On Sat, Apr 3, 2021 at 8:34 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>
>>> 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 <bruce(at)momjian(dot)us> https://momjian.us
>>> EDB https://enterprisedb.com
>>>
>>> If only the physical world exists, free will is an illusion.
>>>
>>>