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 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

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.
>>
>>