Home > mailing lists

Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration. - Mailing list pgsql-performance

From	Pavel Stehule
Subject	Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.
Date	April 3, 2021 18:35:36
Msg-id	CAFj8pRAG1x1ztqDQ1Y3A1bqnSkzX208CNo7Y037CYJ5Y5iLK7w@mail.gmail.com Whole thread Raw
In response to	Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration. (aditya desai <admad123@gmail.com>)
List	pgsql-performance

Tree view

so 3. 4. 2021 v 17:30 odesílatel aditya desai <admad123@gmail.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@gmail.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@momjian.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@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

pgsql-performance by date:

From: aditya desai
Date: 03 April 2021, 18:33:42
Subject: Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

From: Bruce Momjian
Date: 03 April 2021, 18:38:12
Subject: Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration.

Re: SELECT Query taking 200 ms on PostgreSQL compared to 4 ms on Oracle after migration. - Mailing list pgsql-performance

Previous

Next