pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres DB Slowness
17+ messages / 9 participants
[nested] [flat]
* Postgres DB Slowness
@ 2019-08-22 14:04 soumik.bhattacharjee@kpn.com
0 siblings, 4 replies; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-22 14:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-admin
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Your immediate response is appreciated.
Thanks
Soumik
Attachments:
Postgres-DB_Parameters.txt (text/plain, 39.6K) download
name | setting | description
----------------------------------------+---------------------------------------+---------------------------------------------------------------------------------------
----------------------------------------
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_command | ~/bin/archive_command PG0002P %p %f | Sets the shell command that will be called to archive a WAL file.
archive_mode | on | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next WAL file if a new file has not been started within N secon
ds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltupl
es.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem | 1MB | Sets the maximum memory to be used by each autovacuum worker process.
backend_flush_after | 0 | Number of pages after which previously performed writes are flushed to disk.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_flush_after | 512kB | Number of pages after which previously performed writes are flushed to disk.
bgwriter_lru_maxpages | 1000 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour | off | Enables advertising the server via Bonjour.
bonjour_name | | Sets the Bonjour service name.
bytea_output | hex | Sets the output format for bytea.
check_function_bodies | on | Check function bodies during CREATE FUNCTION.
checkpoint_completion_target | 0.9 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interva
l.
checkpoint_flush_after | 256kB | Number of pages after which previously performed writes are flushed to disk.
checkpoint_timeout | 15min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 1min | Enables warnings if checkpoint segments are filled more frequently than this.
client_encoding | UTF8 | Sets the client's character set encoding.
client_min_messages | notice | Sets the message levels that are sent to the client.
cluster_name | PG0002P | Sets the name of the cluster, which is included in the process title.
commit_delay | 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings | 5 | Sets the minimum concurrent open transactions before performing commit_delay.
config_file | /pg01/ss/11/PG0002P/postgresql.conf | Sets the server's main configuration file.
constraint_exclusion | partition | Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index
scan.
cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction | 0.1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
data_checksums | on | Shows whether data checksums are turned on for this cluster.
data_directory | /pg01/ss/11/PG0002P | Sets the server's data directory.
data_directory_mode | 0700 | Mode of the data directory.
data_sync_retry | off | Whether to continue running after a failure to sync data files.
DateStyle | ISO, MDY | Sets the display format for date and time values.
db_user_namespace | off | Enables per-database user names.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
debug_assertions | off | Shows whether the running server has assertion checks enabled.
debug_pretty_print | on | Indents parse and plan tree displays.
debug_print_parse | off | Logs each query's parse tree.
debug_print_plan | off | Logs each query's execution plan.
debug_print_rewritten | off | Logs each query's rewritten parse tree.
default_statistics_target | 500 | Sets the default statistics target.
default_tablespace | | Sets the default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.english | Sets default text search configuration.
default_transaction_deferrable | off | Sets the default deferrable status of new transactions.
default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
default_transaction_read_only | off | Sets the default read-only status of new transactions.
default_with_oids | off | Create new tables with OIDs by default.
dynamic_library_path | $libdir | Sets the path for dynamically loadable modules.
dynamic_shared_memory_type | posix | Selects the dynamic shared memory implementation used.
effective_cache_size | 20GB | Sets the planner's assumption about the total size of the data caches.
effective_io_concurrency | 1000 | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
enable_gathermerge | on | Enables the planner's use of gather merge plans.
enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner's use of hash join plans.
enable_indexonlyscan | on | Enables the planner's use of index-only-scan plans.
enable_indexscan | on | Enables the planner's use of index-scan plans.
enable_material | on | Enables the planner's use of materialization.
enable_mergejoin | on | Enables the planner's use of merge join plans.
enable_nestloop | on | Enables the planner's use of nested-loop join plans.
enable_parallel_append | on | Enables the planner's use of parallel append plans.
enable_parallel_hash | on | Enables the planner's use of parallel hash plans.
enable_partition_pruning | on | Enable plan-time and run-time partition pruning.
enable_partitionwise_aggregate | off | Enables partitionwise aggregation and grouping.
enable_partitionwise_join | off | Enables partitionwise join.
enable_seqscan | on | Enables the planner's use of sequential-scan plans.
enable_sort | on | Enables the planner's use of explicit sort steps.
enable_tidscan | on | Enables the planner's use of TID scan plans.
escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
event_source | PostgreSQL | Sets the application name used to identify PostgreSQL messages in the event log.
exit_on_error | off | Terminate session on any error.
external_pid_file | | Writes the postmaster PID to the specified file.
extra_float_digits | 0 | Sets the number of digits displayed for floating-point values.
force_parallel_mode | off | Forces use of parallel query facilities.
from_collapse_limit | 8 | Sets the FROM-list size beyond which subqueries are not collapsed.
fsync | on | Forces synchronization of updates to disk.
full_page_writes | on | Writes full pages to WAL when first modified after a checkpoint.
geqo | on | Enables genetic query optimization.
geqo_effort | 5 | GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations | 0 | GEQO: number of iterations of the algorithm.
geqo_pool_size | 0 | GEQO: number of individuals in the population.
geqo_seed | 0 | GEQO: seed for random path selection.
geqo_selection_bias | 2 | GEQO: selective pressure within the population.
geqo_threshold | 12 | Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit | 0 | Sets the maximum allowed result for exact search by GIN.
gin_pending_list_limit | 4MB | Sets the maximum size of the pending list for GIN index.
hba_file | /pg01/ss/11/PG0002P/pg_hba.conf | Sets the server's "hba" configuration file.
hot_standby | on | Allows connections and queries during recovery.
hot_standby_feedback | off | Allows feedback from a hot standby to the primary that will avoid query conflicts.
huge_pages | try | Use of huge pages on Linux or Windows.
ident_file | /pg01/ss/11/PG0002P/pg_ident.conf | Sets the server's "ident" configuration file.
idle_in_transaction_session_timeout | 0 | Sets the maximum allowed duration of any idling transaction.
ignore_checksum_failure | off | Continues processing after a checksum failure.
ignore_system_indexes | off | Disables reading from system indexes.
integer_datetimes | on | Datetimes are integer based.
IntervalStyle | postgres | Sets the display format for interval values.
jit | off | Allow JIT compilation.
jit_above_cost | 100000 | Perform JIT compilation if query is more expensive.
jit_debugging_support | off | Register JIT compiled function with debugger.
jit_dump_bitcode | off | Write out LLVM bitcode to facilitate JIT debugging.
jit_expressions | on | Allow JIT compilation of expressions.
jit_inline_above_cost | 500000 | Perform JIT inlining if query is more expensive.
jit_optimize_above_cost | 500000 | Optimize JITed functions if query is more expensive.
jit_profiling_support | off | Register JIT compiled function with perf profiler.
jit_provider | llvmjit | JIT provider to use.
jit_tuple_deforming | on | Allow JIT compilation of tuple deforming.
join_collapse_limit | 8 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users | off | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Sets the location of the Kerberos server key file.
lc_collate | en_US.UTF-8 | Shows the collation order locale.
lc_ctype | en_US.UTF-8 | Shows the character classification and case conversion locale.
lc_messages | en_US.UTF-8 | Sets the language in which messages are displayed.
lc_monetary | en_US.UTF-8 | Sets the locale for formatting monetary amounts.
lc_numeric | en_US.UTF-8 | Sets the locale for formatting numbers.
lc_time | en_US.UTF-8 | Sets the locale for formatting date and time values.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
lo_compat_privileges | off | Enables backward compatibility mode for privilege checks on large objects.
local_preload_libraries | | Lists unprivileged shared libraries to preload into each backend.
lock_timeout | 0 | Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration | 0 | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | on | Logs each checkpoint.
log_connections | off | Logs each successful connection.
log_destination | stderr | Sets the destination for server log output.
log_directory | log | Sets the destination directory for log files.
log_disconnections | off | Logs end of a session, including duration.
log_duration | off | Logs the duration of each completed SQL statement.
log_error_verbosity | default | Sets the verbosity of logged messages.
log_executor_stats | off | Writes executor performance statistics to the server log.
log_file_mode | 0600 | Sets the file permissions for log files.
log_filename | postgresql-%a.log | Sets the file name pattern for log files.
log_hostname | off | Logs the host name in the connection logs.
log_line_prefix | %m [%p] | Controls information prefixed to each log line.
log_lock_waits | on | Logs long lock waits.
log_min_duration_statement | 2s | Sets the minimum execution time above which statements will be logged.
log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
log_min_messages | warning | Sets the message levels that are logged.
log_parser_stats | off | Writes parser performance statistics to the server log.
log_planner_stats | off | Writes planner performance statistics to the server log.
log_replication_commands | off | Logs each replication command.
log_rotation_age | 1d | Automatic log file rotation will occur after N minutes.
log_rotation_size | 0 | Automatic log file rotation will occur after N kilobytes.
log_statement | none | Sets the type of statements logged.
log_statement_stats | off | Writes cumulative performance statistics to the server log.
log_temp_files | 20MB | Log the use of temporary files larger than this number of kilobytes.
log_timezone | Europe/Amsterdam | Sets the time zone to use in log messages.
log_truncate_on_rotation | on | Truncate existing log files of same name during log rotation.
logging_collector | on | Start a subprocess to capture stderr output and/or csvlogs into log files.
maintenance_work_mem | 2GB | Sets the maximum memory to be used for maintenance operations.
max_connections | 8000 | Sets the maximum number of concurrent connections.
max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
max_function_args | 100 | Shows the maximum number of function arguments.
max_identifier_length | 63 | Shows the maximum identifier length.
max_index_keys | 32 | Shows the maximum number of index keys.
max_locks_per_transaction | 64 | Sets the maximum number of locks per transaction.
max_logical_replication_workers | 6 | Maximum number of logical replication worker processes.
max_parallel_maintenance_workers | 2 | Sets the maximum number of parallel processes per maintenance operation.
max_parallel_workers | 32 | Sets the maximum number of parallel workers that can be active at one time.
max_parallel_workers_per_gather | 16 | Sets the maximum number of parallel processes per executor node.
max_pred_locks_per_page | 2 | Sets the maximum number of predicate-locked tuples per page.
max_pred_locks_per_relation | -2 | Sets the maximum number of predicate-locked pages and tuples per relation.
max_pred_locks_per_transaction | 64 | Sets the maximum number of predicate locks per transaction.
max_prepared_transactions | 0 | Sets the maximum number of simultaneously prepared transactions.
max_replication_slots | 10 | Sets the maximum number of simultaneously defined replication slots.
max_stack_depth | 2MB | Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processin
g archived WAL data.
max_standby_streaming_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processin
g streamed WAL data.
max_sync_workers_per_subscription | 2 | Maximum number of table synchronization workers per subscription.
max_wal_senders | 5 | Sets the maximum number of simultaneously running WAL sender processes.
max_wal_size | 15GB | Sets the WAL size that triggers a checkpoint.
max_worker_processes | 32 | Maximum number of concurrent worker processes.
min_parallel_index_scan_size | 512kB | Sets the minimum amount of index data for a parallel scan.
min_parallel_table_scan_size | 4MB | Sets the minimum amount of table data for a parallel scan.
min_wal_size | 5GB | Sets the minimum size to shrink the WAL to.
old_snapshot_threshold | -1 | Time before a snapshot is too old to read pages changed after the snapshot was taken.
operator_precedence_warning | off | Emit a warning for constructs that changed meaning since PostgreSQL 9.4.
parallel_leader_participation | off | Controls whether Gather and Gather Merge also run subplans.
parallel_setup_cost | 100 | Sets the planner's estimate of the cost of starting up worker processes for parallel q
uery.
parallel_tuple_cost | 0.1 | Sets the planner's estimate of the cost of passing each tuple (row) from worker to mas
ter backend.
password_encryption | scram-sha-256 | Encrypt passwords.
port | 5432 | Sets the TCP port the server listens on.
post_auth_delay | 0 | Waits N seconds on connection startup after authentication.
pre_auth_delay | 0 | Waits N seconds on connection startup before authentication.
quote_all_identifiers | off | When generating SQL fragments, quote all identifiers.
random_page_cost | 1.2 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
restart_after_crash | on | Reinitialize server after backend crash.
row_security | on | Enable row security.
search_path | "$user", public | Sets the schema search order for names that are not schema-qualified.
segment_size | 1GB | Shows the number of pages per disk file.
seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding | UTF8 | Sets the server (database) character set encoding.
server_version | 11.2 | Shows the server version.
server_version_num | 110002 | Shows the server version as an integer.
session_preload_libraries | | Lists shared libraries to preload into each backend.
session_replication_role | origin | Sets the session's behavior for triggers and rewrite rules.
shared_buffers | 15GB | Sets the number of shared memory buffers used by the server.
shared_preload_libraries | | Lists shared libraries to preload into server.
ssl | off | Enables SSL connections.
ssl_ca_file | | Location of the SSL certificate authority file.
ssl_cert_file | server.crt | Location of the SSL server certificate file.
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Sets the list of allowed SSL ciphers.
ssl_crl_file | | Location of the SSL certificate revocation list file.
ssl_dh_params_file | | Location of the SSL DH parameters file.
ssl_ecdh_curve | prime256v1 | Sets the curve to use for ECDH.
ssl_key_file | server.key | Location of the SSL server private key file.
ssl_passphrase_command | | Command to obtain passphrases for SSL.
ssl_passphrase_command_supports_reload | off | Also use ssl_passphrase_command during server reload.
ssl_prefer_server_ciphers | on | Give priority to server ciphersuite order.
standard_conforming_strings | on | Causes '...' strings to treat backslashes literally.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
stats_temp_directory | pg_stat_tmp | Writes temporary statistics files to the specified directory.
superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
synchronize_seqscans | on | Enable synchronized sequential scans.
synchronous_commit | on | Sets the current transaction's synchronization level.
synchronous_standby_names | | Number of synchronous standbys and list of names of potential synchronous ones.
syslog_facility | local0 | Sets the syslog "facility" to be used when syslog enabled.
syslog_ident | postgres | Sets the program name used to identify PostgreSQL messages in syslog.
syslog_sequence_numbers | on | Add sequence number to syslog messages to avoid duplicate suppression.
syslog_split_messages | on | Split messages sent to syslog by lines and to fit into 1024 bytes.
tcp_keepalives_count | 9 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 7200 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 75 | Time between TCP keepalive retransmits.
temp_buffers | 800MB | Sets the maximum number of temporary buffers used by each session.
temp_file_limit | -1 | Limits the total size of all temporary files used by each process.
temp_tablespaces | | Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone | Europe/Amsterdam | Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations | Default | Selects a file of time zone abbreviations.
trace_notify | off | Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages | log | Enables logging of recovery-related debugging information.
trace_sort | off | Emit information about resource usage in sorting.
track_activities | on | Collects information about executing commands.
track_activity_query_size | 1kB | Sets the size reserved for pg_stat_activity.query, in bytes.
track_commit_timestamp | off | Collects transaction commit time.
track_counts | on | Collects statistics on database activity.
track_functions | none | Collects function-level statistics on database activity.
track_io_timing | off | Collects timing statistics for database I/O activity.
transaction_deferrable | off | Whether to defer a read-only serializable transaction until it can be executed with no
possible serialization failures.
transaction_isolation | read committed | Sets the current transaction's isolation level.
transaction_read_only | off | Sets the current transaction's read-only status.
transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
unix_socket_directories | /var/run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
update_process_title | on | Updates the process title to show the active SQL command.
vacuum_cleanup_index_scale_factor | 0.1 | Number of tuple inserts prior to index cleanup as a fraction of reltuples.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age | 0 | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age | 5000000 | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_block_size | 8192 | Shows the block size in the write ahead log.
wal_buffers | 1GB | Sets the number of disk-page buffers in shared memory for WAL.
wal_compression | off | Compresses full-page writes written in WAL file.
wal_consistency_checking | | Sets the WAL resource managers for which WAL consistency checks are done.
wal_keep_segments | 64 | Sets the number of WAL files held for standby servers.
wal_level | replica | Set the level of information written to the WAL.
wal_log_hints | off | Writes full pages to WAL when first modified after a checkpoint, even for a non-critic
al modifications.
wal_receiver_status_interval | 10s | Sets the maximum interval between WAL receiver status reports to the primary.
wal_receiver_timeout | 1min | Sets the maximum wait time to receive data from the primary.
wal_retrieve_retry_interval | 5s | Sets the time to wait before retrying to retrieve WAL after a failed attempt.
wal_segment_size | 16MB | Shows the size of write ahead log segments.
wal_sender_timeout | 1min | Sets the maximum time to wait for WAL replication.
wal_sync_method | fdatasync | Selects the method used for forcing WAL updates to disk.
wal_writer_delay | 200ms | Time between WAL flushes performed in the WAL writer.
wal_writer_flush_after | 1MB | Amount of WAL written out by WAL writer that triggers a flush.
work_mem | 1GB | Sets the maximum memory to be used for query workspaces.
xmlbinary | base64 | Sets how binary values are to be encoded in XML.
xmloption | content | Sets whether XML data in implicit parsing and serialization operations is to be consid
ered as documents or content fragments.
zero_damaged_pages | off | Continues processing past damaged page headers.
* FW: Postgres DB Slowness
@ 2019-08-22 14:09 soumik.bhattacharjee@kpn.com
parent: soumik.bhattacharjee@kpn.com
3 siblings, 0 replies; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-22 14:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-admin
Correction in version
# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 4:05 PM
To: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
Subject: Postgres DB Slowness
Importance: High
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Your immediate response is appreciated.
Thanks
Soumik
Attachments:
Postgres-DB_Parameters.txt (text/plain, 39.6K) download
name | setting | description
----------------------------------------+---------------------------------------+---------------------------------------------------------------------------------------
----------------------------------------
allow_system_table_mods | off | Allows modifications of the structure of system tables.
application_name | psql | Sets the application name to be reported in statistics and logs.
archive_command | ~/bin/archive_command PG0002P %p %f | Sets the shell command that will be called to archive a WAL file.
archive_mode | on | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next WAL file if a new file has not been started within N secon
ds.
array_nulls | on | Enable input of NULL elements in arrays.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltupl
es.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.
autovacuum_work_mem | 1MB | Sets the maximum memory to be used by each autovacuum worker process.
backend_flush_after | 0 | Number of pages after which previously performed writes are flushed to disk.
backslash_quote | safe_encoding | Sets whether "\'" is allowed in string literals.
bgwriter_delay | 200ms | Background writer sleep time between rounds.
bgwriter_flush_after | 512kB | Number of pages after which previously performed writes are flushed to disk.
bgwriter_lru_maxpages | 1000 | Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier | 2 | Multiple of the average buffer usage to free per round.
block_size | 8192 | Shows the size of a disk block.
bonjour | off | Enables advertising the server via Bonjour.
bonjour_name | | Sets the Bonjour service name.
bytea_output | hex | Sets the output format for bytea.
check_function_bodies | on | Check function bodies during CREATE FUNCTION.
checkpoint_completion_target | 0.9 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interva
l.
checkpoint_flush_after | 256kB | Number of pages after which previously performed writes are flushed to disk.
checkpoint_timeout | 15min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 1min | Enables warnings if checkpoint segments are filled more frequently than this.
client_encoding | UTF8 | Sets the client's character set encoding.
client_min_messages | notice | Sets the message levels that are sent to the client.
cluster_name | PG0002P | Sets the name of the cluster, which is included in the process title.
commit_delay | 0 | Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings | 5 | Sets the minimum concurrent open transactions before performing commit_delay.
config_file | /pg01/ss/11/PG0002P/postgresql.conf | Sets the server's main configuration file.
constraint_exclusion | partition | Enables the planner to use constraints to optimize queries.
cpu_index_tuple_cost | 0.005 | Sets the planner's estimate of the cost of processing each index entry during an index
scan.
cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call.
cpu_tuple_cost | 0.01 | Sets the planner's estimate of the cost of processing each tuple (row).
cursor_tuple_fraction | 0.1 | Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
data_checksums | on | Shows whether data checksums are turned on for this cluster.
data_directory | /pg01/ss/11/PG0002P | Sets the server's data directory.
data_directory_mode | 0700 | Mode of the data directory.
data_sync_retry | off | Whether to continue running after a failure to sync data files.
DateStyle | ISO, MDY | Sets the display format for date and time values.
db_user_namespace | off | Enables per-database user names.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
debug_assertions | off | Shows whether the running server has assertion checks enabled.
debug_pretty_print | on | Indents parse and plan tree displays.
debug_print_parse | off | Logs each query's parse tree.
debug_print_plan | off | Logs each query's execution plan.
debug_print_rewritten | off | Logs each query's rewritten parse tree.
default_statistics_target | 500 | Sets the default statistics target.
default_tablespace | | Sets the default tablespace to create tables and indexes in.
default_text_search_config | pg_catalog.english | Sets default text search configuration.
default_transaction_deferrable | off | Sets the default deferrable status of new transactions.
default_transaction_isolation | read committed | Sets the transaction isolation level of each new transaction.
default_transaction_read_only | off | Sets the default read-only status of new transactions.
default_with_oids | off | Create new tables with OIDs by default.
dynamic_library_path | $libdir | Sets the path for dynamically loadable modules.
dynamic_shared_memory_type | posix | Selects the dynamic shared memory implementation used.
effective_cache_size | 20GB | Sets the planner's assumption about the total size of the data caches.
effective_io_concurrency | 1000 | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
enable_bitmapscan | on | Enables the planner's use of bitmap-scan plans.
enable_gathermerge | on | Enables the planner's use of gather merge plans.
enable_hashagg | on | Enables the planner's use of hashed aggregation plans.
enable_hashjoin | on | Enables the planner's use of hash join plans.
enable_indexonlyscan | on | Enables the planner's use of index-only-scan plans.
enable_indexscan | on | Enables the planner's use of index-scan plans.
enable_material | on | Enables the planner's use of materialization.
enable_mergejoin | on | Enables the planner's use of merge join plans.
enable_nestloop | on | Enables the planner's use of nested-loop join plans.
enable_parallel_append | on | Enables the planner's use of parallel append plans.
enable_parallel_hash | on | Enables the planner's use of parallel hash plans.
enable_partition_pruning | on | Enable plan-time and run-time partition pruning.
enable_partitionwise_aggregate | off | Enables partitionwise aggregation and grouping.
enable_partitionwise_join | off | Enables partitionwise join.
enable_seqscan | on | Enables the planner's use of sequential-scan plans.
enable_sort | on | Enables the planner's use of explicit sort steps.
enable_tidscan | on | Enables the planner's use of TID scan plans.
escape_string_warning | on | Warn about backslash escapes in ordinary string literals.
event_source | PostgreSQL | Sets the application name used to identify PostgreSQL messages in the event log.
exit_on_error | off | Terminate session on any error.
external_pid_file | | Writes the postmaster PID to the specified file.
extra_float_digits | 0 | Sets the number of digits displayed for floating-point values.
force_parallel_mode | off | Forces use of parallel query facilities.
from_collapse_limit | 8 | Sets the FROM-list size beyond which subqueries are not collapsed.
fsync | on | Forces synchronization of updates to disk.
full_page_writes | on | Writes full pages to WAL when first modified after a checkpoint.
geqo | on | Enables genetic query optimization.
geqo_effort | 5 | GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations | 0 | GEQO: number of iterations of the algorithm.
geqo_pool_size | 0 | GEQO: number of individuals in the population.
geqo_seed | 0 | GEQO: seed for random path selection.
geqo_selection_bias | 2 | GEQO: selective pressure within the population.
geqo_threshold | 12 | Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit | 0 | Sets the maximum allowed result for exact search by GIN.
gin_pending_list_limit | 4MB | Sets the maximum size of the pending list for GIN index.
hba_file | /pg01/ss/11/PG0002P/pg_hba.conf | Sets the server's "hba" configuration file.
hot_standby | on | Allows connections and queries during recovery.
hot_standby_feedback | off | Allows feedback from a hot standby to the primary that will avoid query conflicts.
huge_pages | try | Use of huge pages on Linux or Windows.
ident_file | /pg01/ss/11/PG0002P/pg_ident.conf | Sets the server's "ident" configuration file.
idle_in_transaction_session_timeout | 0 | Sets the maximum allowed duration of any idling transaction.
ignore_checksum_failure | off | Continues processing after a checksum failure.
ignore_system_indexes | off | Disables reading from system indexes.
integer_datetimes | on | Datetimes are integer based.
IntervalStyle | postgres | Sets the display format for interval values.
jit | off | Allow JIT compilation.
jit_above_cost | 100000 | Perform JIT compilation if query is more expensive.
jit_debugging_support | off | Register JIT compiled function with debugger.
jit_dump_bitcode | off | Write out LLVM bitcode to facilitate JIT debugging.
jit_expressions | on | Allow JIT compilation of expressions.
jit_inline_above_cost | 500000 | Perform JIT inlining if query is more expensive.
jit_optimize_above_cost | 500000 | Optimize JITed functions if query is more expensive.
jit_profiling_support | off | Register JIT compiled function with perf profiler.
jit_provider | llvmjit | JIT provider to use.
jit_tuple_deforming | on | Allow JIT compilation of tuple deforming.
join_collapse_limit | 8 | Sets the FROM-list size beyond which JOIN constructs are not flattened.
krb_caseins_users | off | Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab | Sets the location of the Kerberos server key file.
lc_collate | en_US.UTF-8 | Shows the collation order locale.
lc_ctype | en_US.UTF-8 | Shows the character classification and case conversion locale.
lc_messages | en_US.UTF-8 | Sets the language in which messages are displayed.
lc_monetary | en_US.UTF-8 | Sets the locale for formatting monetary amounts.
lc_numeric | en_US.UTF-8 | Sets the locale for formatting numbers.
lc_time | en_US.UTF-8 | Sets the locale for formatting date and time values.
listen_addresses | * | Sets the host name or IP address(es) to listen to.
lo_compat_privileges | off | Enables backward compatibility mode for privilege checks on large objects.
local_preload_libraries | | Lists unprivileged shared libraries to preload into each backend.
lock_timeout | 0 | Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration | 0 | Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints | on | Logs each checkpoint.
log_connections | off | Logs each successful connection.
log_destination | stderr | Sets the destination for server log output.
log_directory | log | Sets the destination directory for log files.
log_disconnections | off | Logs end of a session, including duration.
log_duration | off | Logs the duration of each completed SQL statement.
log_error_verbosity | default | Sets the verbosity of logged messages.
log_executor_stats | off | Writes executor performance statistics to the server log.
log_file_mode | 0600 | Sets the file permissions for log files.
log_filename | postgresql-%a.log | Sets the file name pattern for log files.
log_hostname | off | Logs the host name in the connection logs.
log_line_prefix | %m [%p] | Controls information prefixed to each log line.
log_lock_waits | on | Logs long lock waits.
log_min_duration_statement | 2s | Sets the minimum execution time above which statements will be logged.
log_min_error_statement | error | Causes all statements generating error at or above this level to be logged.
log_min_messages | warning | Sets the message levels that are logged.
log_parser_stats | off | Writes parser performance statistics to the server log.
log_planner_stats | off | Writes planner performance statistics to the server log.
log_replication_commands | off | Logs each replication command.
log_rotation_age | 1d | Automatic log file rotation will occur after N minutes.
log_rotation_size | 0 | Automatic log file rotation will occur after N kilobytes.
log_statement | none | Sets the type of statements logged.
log_statement_stats | off | Writes cumulative performance statistics to the server log.
log_temp_files | 20MB | Log the use of temporary files larger than this number of kilobytes.
log_timezone | Europe/Amsterdam | Sets the time zone to use in log messages.
log_truncate_on_rotation | on | Truncate existing log files of same name during log rotation.
logging_collector | on | Start a subprocess to capture stderr output and/or csvlogs into log files.
maintenance_work_mem | 2GB | Sets the maximum memory to be used for maintenance operations.
max_connections | 8000 | Sets the maximum number of concurrent connections.
max_files_per_process | 1000 | Sets the maximum number of simultaneously open files for each server process.
max_function_args | 100 | Shows the maximum number of function arguments.
max_identifier_length | 63 | Shows the maximum identifier length.
max_index_keys | 32 | Shows the maximum number of index keys.
max_locks_per_transaction | 64 | Sets the maximum number of locks per transaction.
max_logical_replication_workers | 6 | Maximum number of logical replication worker processes.
max_parallel_maintenance_workers | 2 | Sets the maximum number of parallel processes per maintenance operation.
max_parallel_workers | 32 | Sets the maximum number of parallel workers that can be active at one time.
max_parallel_workers_per_gather | 16 | Sets the maximum number of parallel processes per executor node.
max_pred_locks_per_page | 2 | Sets the maximum number of predicate-locked tuples per page.
max_pred_locks_per_relation | -2 | Sets the maximum number of predicate-locked pages and tuples per relation.
max_pred_locks_per_transaction | 64 | Sets the maximum number of predicate locks per transaction.
max_prepared_transactions | 0 | Sets the maximum number of simultaneously prepared transactions.
max_replication_slots | 10 | Sets the maximum number of simultaneously defined replication slots.
max_stack_depth | 2MB | Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processin
g archived WAL data.
max_standby_streaming_delay | 30s | Sets the maximum delay before canceling queries when a hot standby server is processin
g streamed WAL data.
max_sync_workers_per_subscription | 2 | Maximum number of table synchronization workers per subscription.
max_wal_senders | 5 | Sets the maximum number of simultaneously running WAL sender processes.
max_wal_size | 15GB | Sets the WAL size that triggers a checkpoint.
max_worker_processes | 32 | Maximum number of concurrent worker processes.
min_parallel_index_scan_size | 512kB | Sets the minimum amount of index data for a parallel scan.
min_parallel_table_scan_size | 4MB | Sets the minimum amount of table data for a parallel scan.
min_wal_size | 5GB | Sets the minimum size to shrink the WAL to.
old_snapshot_threshold | -1 | Time before a snapshot is too old to read pages changed after the snapshot was taken.
operator_precedence_warning | off | Emit a warning for constructs that changed meaning since PostgreSQL 9.4.
parallel_leader_participation | off | Controls whether Gather and Gather Merge also run subplans.
parallel_setup_cost | 100 | Sets the planner's estimate of the cost of starting up worker processes for parallel q
uery.
parallel_tuple_cost | 0.1 | Sets the planner's estimate of the cost of passing each tuple (row) from worker to mas
ter backend.
password_encryption | scram-sha-256 | Encrypt passwords.
port | 5432 | Sets the TCP port the server listens on.
post_auth_delay | 0 | Waits N seconds on connection startup after authentication.
pre_auth_delay | 0 | Waits N seconds on connection startup before authentication.
quote_all_identifiers | off | When generating SQL fragments, quote all identifiers.
random_page_cost | 1.2 | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
restart_after_crash | on | Reinitialize server after backend crash.
row_security | on | Enable row security.
search_path | "$user", public | Sets the schema search order for names that are not schema-qualified.
segment_size | 1GB | Shows the number of pages per disk file.
seq_page_cost | 1 | Sets the planner's estimate of the cost of a sequentially fetched disk page.
server_encoding | UTF8 | Sets the server (database) character set encoding.
server_version | 11.2 | Shows the server version.
server_version_num | 110002 | Shows the server version as an integer.
session_preload_libraries | | Lists shared libraries to preload into each backend.
session_replication_role | origin | Sets the session's behavior for triggers and rewrite rules.
shared_buffers | 15GB | Sets the number of shared memory buffers used by the server.
shared_preload_libraries | | Lists shared libraries to preload into server.
ssl | off | Enables SSL connections.
ssl_ca_file | | Location of the SSL certificate authority file.
ssl_cert_file | server.crt | Location of the SSL server certificate file.
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Sets the list of allowed SSL ciphers.
ssl_crl_file | | Location of the SSL certificate revocation list file.
ssl_dh_params_file | | Location of the SSL DH parameters file.
ssl_ecdh_curve | prime256v1 | Sets the curve to use for ECDH.
ssl_key_file | server.key | Location of the SSL server private key file.
ssl_passphrase_command | | Command to obtain passphrases for SSL.
ssl_passphrase_command_supports_reload | off | Also use ssl_passphrase_command during server reload.
ssl_prefer_server_ciphers | on | Give priority to server ciphersuite order.
standard_conforming_strings | on | Causes '...' strings to treat backslashes literally.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
stats_temp_directory | pg_stat_tmp | Writes temporary statistics files to the specified directory.
superuser_reserved_connections | 3 | Sets the number of connection slots reserved for superusers.
synchronize_seqscans | on | Enable synchronized sequential scans.
synchronous_commit | on | Sets the current transaction's synchronization level.
synchronous_standby_names | | Number of synchronous standbys and list of names of potential synchronous ones.
syslog_facility | local0 | Sets the syslog "facility" to be used when syslog enabled.
syslog_ident | postgres | Sets the program name used to identify PostgreSQL messages in syslog.
syslog_sequence_numbers | on | Add sequence number to syslog messages to avoid duplicate suppression.
syslog_split_messages | on | Split messages sent to syslog by lines and to fit into 1024 bytes.
tcp_keepalives_count | 9 | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle | 7200 | Time between issuing TCP keepalives.
tcp_keepalives_interval | 75 | Time between TCP keepalive retransmits.
temp_buffers | 800MB | Sets the maximum number of temporary buffers used by each session.
temp_file_limit | -1 | Limits the total size of all temporary files used by each process.
temp_tablespaces | | Sets the tablespace(s) to use for temporary tables and sort files.
TimeZone | Europe/Amsterdam | Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations | Default | Selects a file of time zone abbreviations.
trace_notify | off | Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages | log | Enables logging of recovery-related debugging information.
trace_sort | off | Emit information about resource usage in sorting.
track_activities | on | Collects information about executing commands.
track_activity_query_size | 1kB | Sets the size reserved for pg_stat_activity.query, in bytes.
track_commit_timestamp | off | Collects transaction commit time.
track_counts | on | Collects statistics on database activity.
track_functions | none | Collects function-level statistics on database activity.
track_io_timing | off | Collects timing statistics for database I/O activity.
transaction_deferrable | off | Whether to defer a read-only serializable transaction until it can be executed with no
possible serialization failures.
transaction_isolation | read committed | Sets the current transaction's isolation level.
transaction_read_only | off | Sets the current transaction's read-only status.
transform_null_equals | off | Treats "expr=NULL" as "expr IS NULL".
unix_socket_directories | /var/run/postgresql, /tmp | Sets the directories where Unix-domain sockets will be created.
unix_socket_group | | Sets the owning group of the Unix-domain socket.
unix_socket_permissions | 0777 | Sets the access permissions of the Unix-domain socket.
update_process_title | on | Updates the process title to show the active SQL command.
vacuum_cleanup_index_scale_factor | 0.1 | Number of tuple inserts prior to index cleanup as a fraction of reltuples.
vacuum_cost_delay | 0 | Vacuum cost delay in milliseconds.
vacuum_cost_limit | 200 | Vacuum cost amount available before napping.
vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age | 0 | Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
vacuum_freeze_min_age | 50000000 | Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age | 5000000 | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples.
wal_block_size | 8192 | Shows the block size in the write ahead log.
wal_buffers | 1GB | Sets the number of disk-page buffers in shared memory for WAL.
wal_compression | off | Compresses full-page writes written in WAL file.
wal_consistency_checking | | Sets the WAL resource managers for which WAL consistency checks are done.
wal_keep_segments | 64 | Sets the number of WAL files held for standby servers.
wal_level | replica | Set the level of information written to the WAL.
wal_log_hints | off | Writes full pages to WAL when first modified after a checkpoint, even for a non-critic
al modifications.
wal_receiver_status_interval | 10s | Sets the maximum interval between WAL receiver status reports to the primary.
wal_receiver_timeout | 1min | Sets the maximum wait time to receive data from the primary.
wal_retrieve_retry_interval | 5s | Sets the time to wait before retrying to retrieve WAL after a failed attempt.
wal_segment_size | 16MB | Shows the size of write ahead log segments.
wal_sender_timeout | 1min | Sets the maximum time to wait for WAL replication.
wal_sync_method | fdatasync | Selects the method used for forcing WAL updates to disk.
wal_writer_delay | 200ms | Time between WAL flushes performed in the WAL writer.
wal_writer_flush_after | 1MB | Amount of WAL written out by WAL writer that triggers a flush.
work_mem | 1GB | Sets the maximum memory to be used for query workspaces.
xmlbinary | base64 | Sets how binary values are to be encoded in XML.
xmloption | content | Sets whether XML data in implicit parsing and serialization operations is to be consid
ered as documents or content fragments.
zero_damaged_pages | off | Continues processing past damaged page headers.
* Re: Postgres DB Slowness
@ 2019-08-22 14:25 Ron <ronljohnsonjr@gmail.com>
parent: soumik.bhattacharjee@kpn.com
3 siblings, 2 replies; 17+ messages in thread
From: Ron @ 2019-08-22 14:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com wrote:
>
> Hello Members,
>
> We have the below PostgreSQL database recently migrated from Oracle.
>
> The postgres DB parameters are *_attached_* here.
>
> # DB Version: 10
>
> # OS Type: Linux
>
> # Total Memory (RAM): 30 GB
>
> #CPU
>
> nproc --all
>
> 2
>
> There is huge slowness in the database now with any queries.
>
> *Oracle*- Select * from TABLENAME- in takes 0.009 milliseconds
>
> *PostgreSQL*- Same query takes more than 2 minutes.
>
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-22 14:33 Perry Chandler <Perry.Chandler@taxcalc.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Perry Chandler @ 2019-08-22 14:33 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Rebuilt all of the indexes?
From: Ron <ronljohnsonjr@gmail.com>
Sent: 22 August 2019 15:26
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
--
Angular momentum makes the world go 'round.
TaxCalc is a trading name of Acorah Software Products Limited, a company registered in England and Wales number 03948264. Registered Office: Rubra One, Fishponds Road, Wokingham, Berkshire, RG41 2GY.
Any files attached to this email will have been checked by us with virus detection software before transmission. You should carry out your own virus checks before opening any attachment. Acorah Software Products Limited accepts no liability for any loss or damage which may be caused by software viruses.
The contents of this e-mail (including any attachments) may be CONFIDENTIAL and is for the intended addressee only. If you are not the intended recipient of this e-mail, any disclosure, copying, distribution or use of its contents is strictly prohibited, and you should please notify the sender immediately and then delete it (including any attachments) from your system.
Any unauthorised use, dissemination of the information, or copying of this message is prohibited.
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-22 14:35 soumik.bhattacharjee@kpn.com
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-22 14:35 UTC (permalink / raw)
To: ronljohnsonjr@gmail.com; pgsql-admin@lists.postgresql.org
From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
--
Angular momentum makes the world go 'round.
Yes Ron, we migrated from Oracle as per same structure and indexes.
I did the analyze for 2-3 tables now as per my SQL query I wrote and also ran for one particular table below –
VACUUM (FULL,ANALYZE) table_name.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres DB Slowness
@ 2019-08-22 14:46 Ron <ronljohnsonjr@gmail.com>
parent: soumik.bhattacharjee@kpn.com
0 siblings, 2 replies; 17+ messages in thread
From: Ron @ 2019-08-22 14:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 8/22/19 9:35 AM, soumik.bhattacharjee@kpn.com wrote:
>
> *From:*Ron <ronljohnsonjr@gmail.com>
> *Sent:* Thursday, August 22, 2019 4:26 PM
> *To:* pgsql-admin@lists.postgresql.org
> *Subject:* Re: Postgres DB Slowness
>
> On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com
> <mailto:soumik.bhattacharjee@kpn.com> wrote:
>
> Hello Members,
>
> We have the below PostgreSQL database recently migrated from Oracle.
>
> The postgres DB parameters are *_attached_* here.
>
> # DB Version: 10
>
> # OS Type: Linux
>
> # Total Memory (RAM): 30 GB
>
> #CPU
>
> nproc --all
>
> 2
>
> There is huge slowness in the database now with any queries.
>
> *Oracle*- Select * from TABLENAME- in takes 0.009 milliseconds
>
> *PostgreSQL*- Same query takes more than 2 minutes.
>
>
> Have you validated that the Postgres server has the same indexes?
>
> If so, did you ANALYZE all the tables?
>
> Yes Ron, we migrated from Oracle as per same structure and indexes.
>
> I did the analyze for 2-3 tables now as per my SQL query I wrote and also
> ran for one particular table below –
>
> VACUUM (FULL,ANALYZE) table_name.
>
What does the query plan for that query look like?
What's the table definition?
Is the hardware comparable? (Might it be a SAN or VM issue?)
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-22 15:04 soumik.bhattacharjee@kpn.com
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-22 15:04 UTC (permalink / raw)
To: ronljohnsonjr@gmail.com; pgsql-admin@lists.postgresql.org
From: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres DB Slowness
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
Yes Ron, we migrated from Oracle as per same structure and indexes.
I did the analyze for 2-3 tables now as per my SQL query I wrote and also ran for one particular table below –
VACUUM (FULL,ANALYZE) table_name.
What does the query plan for that query look like?
What's the table definition?
Is the hardware comparable? (Might it be a SAN or VM issue?)
Table definition is attached here.
EXPLAIN SELECT *
FROM npcurren.num_aangesloten_nr;
"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046 width=113)"
- Oracle 11gR2 was hosted on physical HP-UX server last updated 12 years back- legacy system
- Current postgres server- physical
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.6 (Maipo)
$ uname -a
Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[postgres@slnc7r1513 ~]$
Attachments:
Table_Def.txt (text/plain, 4.1K) download
CREATE TABLE npcurren.num_aangesloten_nr
(
anr_nummer_laag character varying(20) COLLATE pg_catalog."default" NOT NULL,
anr_nummer_hoog character varying(20) COLLATE pg_catalog."default" NOT NULL,
fk_eigenaar_nop_int_oper_id character varying(6) COLLATE pg_catalog."default",
fk_sta_id character varying(1) COLLATE pg_catalog."default",
fk_sac_id character varying(1) COLLATE pg_catalog."default",
fk_moeder_hst_spc_id character varying(4) COLLATE pg_catalog."default",
fk_moeder_cgb_code integer,
fk_exploit_nop_int_oper_id character varying(6) COLLATE pg_catalog."default",
fk_dienst_hst_spc_id character varying(4) COLLATE pg_catalog."default",
fk_dienst_cgb_code integer,
anr_soort_dienst character varying(2) COLLATE pg_catalog."default",
anr_roepnummer character varying(20) COLLATE pg_catalog."default",
anr_lri character varying(24) COLLATE pg_catalog."default",
anr_logisch_mutatiemoment timestamp without time zone,
anr_technisch_mutatiemoment timestamp without time zone NOT NULL,
anr_mutatie_gebruiker_id character varying(8) COLLATE pg_catalog."default" NOT NULL,
anr_netnummer character varying(4) COLLATE pg_catalog."default",
anr_heeft_cvi_dienst_jn character varying(1) COLLATE pg_catalog."default",
fk_num_voip_platform_id smallint,
anr_heeft_cvi_zm_dienst_yn character varying(1) COLLATE pg_catalog."default",
anr_cvi_kenmerk character varying(10) COLLATE pg_catalog."default",
anr_cvi_telco character varying(8) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT anr_prx PRIMARY KEY (anr_nummer_laag),
CONSTRAINT sys_c0012384 FOREIGN KEY (fk_sac_id)
REFERENCES npcurren.num_soort_actie (sac_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT sys_c0012385 FOREIGN KEY (fk_sta_id)
REFERENCES npcurren.num_status (sta_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT sys_c0012386 FOREIGN KEY (fk_num_voip_platform_id)
REFERENCES npcurren.num_voip_platform (platform_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE npcurren.num_aangesloten_nr
OWNER to postgres;
-- Index: anr_idx1
-- DROP INDEX npcurren.anr_idx1;
CREATE INDEX anr_idx1
ON npcurren.num_aangesloten_nr USING btree
(fk_moeder_hst_spc_id COLLATE pg_catalog."default", fk_moeder_cgb_code)
TABLESPACE pg_default;
-- Index: anr_idx2
-- DROP INDEX npcurren.anr_idx2;
CREATE INDEX anr_idx2
ON npcurren.num_aangesloten_nr USING btree
(fk_dienst_hst_spc_id COLLATE pg_catalog."default", fk_dienst_cgb_code)
TABLESPACE pg_default;
-- Index: anr_idx3
-- DROP INDEX npcurren.anr_idx3;
CREATE INDEX anr_idx3
ON npcurren.num_aangesloten_nr USING btree
(fk_sac_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx4
-- DROP INDEX npcurren.anr_idx4;
CREATE INDEX anr_idx4
ON npcurren.num_aangesloten_nr USING btree
(fk_sta_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx5
-- DROP INDEX npcurren.anr_idx5;
CREATE INDEX anr_idx5
ON npcurren.num_aangesloten_nr USING btree
(fk_eigenaar_nop_int_oper_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx6
-- DROP INDEX npcurren.anr_idx6;
CREATE INDEX anr_idx6
ON npcurren.num_aangesloten_nr USING btree
(fk_exploit_nop_int_oper_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx7
-- DROP INDEX npcurren.anr_idx7;
CREATE INDEX anr_idx7
ON npcurren.num_aangesloten_nr USING btree
(anr_nummer_hoog COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx8
-- DROP INDEX npcurren.anr_idx8;
CREATE INDEX anr_idx8
ON npcurren.num_aangesloten_nr USING btree
(anr_netnummer COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx9
-- DROP INDEX npcurren.anr_idx9;
CREATE INDEX anr_idx9
ON npcurren.num_aangesloten_nr USING btree
(fk_num_voip_platform_id)
TABLESPACE pg_default;
* RE: Postgres DB Slowness
@ 2019-08-22 15:15 Holger Jakobs <holger@jakobs.com>
parent: soumik.bhattacharjee@kpn.com
0 siblings, 0 replies; 17+ messages in thread
From: Holger Jakobs @ 2019-08-22 15:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Could it be that you are comparing the time until first row to time until all rows have been transferred?
Otherwise the times don't seem sensible.
Am 22. August 2019 17:04:19 MESZ schrieb soumik.bhattacharjee@kpn.com:
>From: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
>Sent: Thursday, August 22, 2019 4:26 PM
>To:
>pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
>Subject: Re: Postgres DB Slowness
>
>On 8/22/19 9:04 AM,
>soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>
>wrote:
>
>
>Hello Members,
>
>We have the below PostgreSQL database recently migrated from Oracle.
>
>The postgres DB parameters are attached here.
>
>
># DB Version: 10
>
># OS Type: Linux
>
># Total Memory (RAM): 30 GB
>#CPU
>nproc --all
>2
>
>There is huge slowness in the database now with any queries.
>
>Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
>PostgreSQL - Same query takes more than 2 minutes.
>
>Have you validated that the Postgres server has the same indexes?
>
>If so, did you ANALYZE all the tables?
>
>Yes Ron, we migrated from Oracle as per same structure and indexes.
>
>I did the analyze for 2-3 tables now as per my SQL query I wrote and
>also ran for one particular table below –
>
>VACUUM (FULL,ANALYZE) table_name.
>
>What does the query plan for that query look like?
>
>What's the table definition?
>
>Is the hardware comparable? (Might it be a SAN or VM issue?)
>
>Table definition is attached here.
>
>EXPLAIN SELECT *
> FROM npcurren.num_aangesloten_nr;
>
>"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046
>width=113)"
>
>
>
>- Oracle 11gR2 was hosted on physical HP-UX server last
>updated 12 years back- legacy system
>
>- Current postgres server- physical
>
>$ cat /etc/redhat-release
>
>Red Hat Enterprise Linux Server release 7.6 (Maipo)
>
>$ uname -a
>
>Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov
>15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
>
>[postgres@slnc7r1513 ~]$
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-22 15:25 soumik.bhattacharjee@kpn.com
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-22 15:25 UTC (permalink / raw)
To: ronljohnsonjr@gmail.com; pgsql-admin@lists.postgresql.org
From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: RE: Postgres DB Slowness
From: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres DB Slowness
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
Yes Ron, we migrated from Oracle as per same structure and indexes.
I did the analyze for 2-3 tables now as per my SQL query I wrote and also ran for one particular table below –
VACUUM (FULL,ANALYZE) table_name.
What does the query plan for that query look like?
What's the table definition?
Is the hardware comparable? (Might it be a SAN or VM issue?)
Table definition is attached here.
EXPLAIN SELECT *
FROM npcurren.num_aangesloten_nr;
"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046 width=113)"
- Oracle 11gR2 was hosted on physical HP-UX server last updated 12 years back- legacy system
- Current postgres server- physical
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.6 (Maipo)
$ uname -a
Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
The select * from query takes all of the 2 CPU’s.
top - 17:24:33 up 42 days, 5:12, 1 user, load average: 0.43, 0.54, 0.57
Tasks: 227 total, 4 running, 223 sleeping, 0 stopped, 0 zombie
%Cpu(s): 35.2 us, 6.9 sy, 0.0 ni, 51.9 id, 0.0 wa, 0.0 hi, 6.0 si, 0.0 st
KiB Mem : 32947032 total, 29998788 free, 375068 used, 2573176 buff/cache
KiB Swap: 2097148 total, 1953380 free, 143768 used. 30803956 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11331 postgres 20 0 5976024 16036 13432 R 92.1 0.0 0:13.98 postgres
11163 postgres 20 0 5981000 16764 9088 S 1.3 0.1 0:01.24 postgres
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres DB Slowness
@ 2019-08-22 17:12 Adarsh Sharma <eddy.adarsh@gmail.com>
parent: soumik.bhattacharjee@kpn.com
1 sibling, 2 replies; 17+ messages in thread
From: Adarsh Sharma @ 2019-08-22 17:12 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin@lists.postgresql.org
Can you please paste explain analyze SELECT *
FROM npcurren.num_aangesloten_nr output?
On Thu, Aug 22, 2019 at 8:57 PM <soumik.bhattacharjee@kpn.com> wrote:
>
>
>
>
> *From:* Bhattacharjee, Soumik
> *Sent:* Thursday, August 22, 2019 5:05 PM
> *To:* 'Ron' <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> *Subject:* RE: Postgres DB Slowness
>
>
>
> *From:* Ron <ronljohnsonjr@gmail.com> <ronljohnsonjr@gmail.com>
> *Sent:* Thursday, August 22, 2019 4:26 PM
> *To:* pgsql-admin@lists.postgresql.org
> *Subject:* Re: Postgres DB Slowness
>
>
>
> On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com wrote:
>
> Hello Members,
>
>
>
> We have the below PostgreSQL database recently migrated from Oracle.
>
>
>
> The postgres DB parameters are *attached* here.
>
>
>
> # DB Version: 10
>
> # OS Type: Linux
>
> # Total Memory (RAM): 30 GB
>
> #CPU
>
> nproc --all
>
> 2
>
>
>
> There is huge slowness in the database now with any queries.
>
>
>
> *Oracle* - Select * from TABLENAME- in takes 0.009 milliseconds
>
> *PostgreSQL* - Same query takes more than 2 minutes.
>
>
> Have you validated that the Postgres server has the same indexes?
>
> If so, did you ANALYZE all the tables?
>
>
>
> Yes Ron, we migrated from Oracle as per same structure and indexes.
>
>
>
> I did the analyze for 2-3 tables now as per my SQL query I wrote and also
> ran for one particular table below –
>
>
>
> VACUUM (FULL,ANALYZE) table_name.
>
>
> What does the query plan for that query look like?
>
> What's the table definition?
>
> Is the hardware comparable? (Might it be a SAN or VM issue?)
>
>
>
> Table definition is attached here.
>
>
>
> EXPLAIN SELECT *
>
> FROM npcurren.num_aangesloten_nr;
>
>
>
> "Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046
> width=113)"
>
>
>
>
>
> - Oracle 11gR2 was hosted on physical HP-UX server last updated
> 12 years back- legacy system
>
> - Current postgres server- physical
>
> $ cat /etc/redhat-release
>
> Red Hat Enterprise Linux Server release 7.6 (Maipo)
>
> $ uname -a
>
> Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15
> 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
>
>
>
>
>
> The select * from query takes all of the 2 CPU’s.
>
>
>
> top - 17:24:33 up 42 days, 5:12, 1 user, load average: 0.43, 0.54, 0.57
>
> Tasks: 227 total, 4 running, 223 sleeping, 0 stopped, 0 zombie
>
> %Cpu(s): 35.2 us, 6.9 sy, 0.0 ni, 51.9 id, 0.0 wa, 0.0 hi, 6.0 si,
> 0.0 st
>
> KiB Mem : 32947032 total, 29998788 free, 375068 used, 2573176 buff/cache
>
> KiB Swap: 2097148 total, 1953380 free, 143768 used. 30803956 avail Mem
>
>
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>
> *11331 postgres 20 0 5976024 16036 13432 R 92.1 0.0 0:13.98
> postgres*
>
> 11163 postgres 20 0 5981000 16764 9088 S 1.3 0.1 0:01.24
> postgres
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres DB Slowness
@ 2019-08-22 20:30 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: soumik.bhattacharjee@kpn.com
1 sibling, 0 replies; 17+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2019-08-22 20:30 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: ronljohnsonjr@gmail.com; pgsql-admin@lists.postgresql.org
On Thu, 22 Aug 2019 15:25:50 +0000
<soumik.bhattacharjee@kpn.com> wrote:
> On 8/22/19 9:04 AM,
> soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
>
> Hello Members,
>
> We have the below PostgreSQL database recently migrated from Oracle.
>
> The postgres DB parameters are attached here.
>
>
> # DB Version: 10
>
> # OS Type: Linux
>
> # Total Memory (RAM): 30 GB
> #CPU
> nproc --all
> 2
>
> There is huge slowness in the database now with any queries.
>
> Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
> PostgreSQL - Same query takes more than 2 minutes.
>
[...]
> Table definition is attached here.
>
> EXPLAIN SELECT *
> FROM npcurren.num_aangesloten_nr;
>
> "Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046
> width=113)"
First, this query can not use any kind of index as it just returns the whole
table.
Second, does your table really have about 10 million rows? If yes, do not
expect to have a result in 0.009 milliseconds. As Holger Jakobs wrote, maybe
you compare the time to return the very first row? I can't believe Oracle can
provide 10Mo rows in 9µs.
[...]
>
> The select * from query takes all of the 2 CPU’s.
>
>
> top - 17:24:33 up 42 days, 5:12, 1 user, load average: 0.43, 0.54, 0.57
> Tasks: 227 total, 4 running, 223 sleeping, 0 stopped, 0 zombie
> %Cpu(s): 35.2 us, 6.9 sy, 0.0 ni, 51.9 id, 0.0 wa, 0.0 hi, 6.0 si, 0.0
> st KiB Mem : 32947032 total, 29998788 free, 375068 used, 2573176 buff/cache
> KiB Swap: 2097148 total, 1953380 free, 143768 used. 30803956 avail Mem
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 11331 postgres 20 0 5976024 16036 13432 R 92.1 0.0 0:13.98 postgres
> 11163 postgres 20 0 5981000 16764 9088 S 1.3 0.1 0:01.24 postgres
No. it takes one core. In top 100% means "one core is burning somewhere". On
your "2 CPU" server, %CPU can go as high as 200%.
Regards,
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres DB Slowness
@ 2019-08-22 20:57 Jeff Janes <jeff.janes@gmail.com>
parent: soumik.bhattacharjee@kpn.com
3 siblings, 0 replies; 17+ messages in thread
From: Jeff Janes @ 2019-08-22 20:57 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin@lists.postgresql.org; pgsql-admin
On Thu, Aug 22, 2019 at 10:05 AM <soumik.bhattacharjee@kpn.com> wrote:
>
> There is huge slowness in the database now with any queries.
>
>
>
> *Oracle* - Select * from TABLENAME- in takes 0.009 milliseconds
>
Oracle is not returning 10 million rows in 0.009 milliseconds.
Perhaps whatever you are doing in Oracle is the equivalent of "EXPLAIN"
without ANALYZE in PostgreSQL, or to PREPARE in PostgreSQL. It just plans
the query without executing it.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-23 08:01 soumik.bhattacharjee@kpn.com
parent: Adarsh Sharma <eddy.adarsh@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-23 08:01 UTC (permalink / raw)
To: eddy.adarsh@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
Hi,
Please find below
"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046 width=113) (actual time=0.102..2226.525 rows=9649110 loops=1)"
"Planning Time: 2.050 ms"
"Execution Time: 2785.262 ms"
From: Adarsh Sharma <eddy.adarsh@gmail.com>
Sent: Thursday, August 22, 2019 7:12 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness
Can you please paste explain analyze SELECT *
FROM npcurren.num_aangesloten_nr output?
On Thu, Aug 22, 2019 at 8:57 PM <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>> wrote:
From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>>; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: RE: Postgres DB Slowness
From: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres DB Slowness
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
Yes Ron, we migrated from Oracle as per same structure and indexes.
I did the analyze for 2-3 tables now as per my SQL query I wrote and also ran for one particular table below –
VACUUM (FULL,ANALYZE) table_name.
What does the query plan for that query look like?
What's the table definition?
Is the hardware comparable? (Might it be a SAN or VM issue?)
Table definition is attached here.
EXPLAIN SELECT *
FROM npcurren.num_aangesloten_nr;
"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046 width=113)"
- Oracle 11gR2 was hosted on physical HP-UX server last updated 12 years back- legacy system
- Current postgres server- physical
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.6 (Maipo)
$ uname -a
Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
The select * from query takes all of the 2 CPU’s.
top - 17:24:33 up 42 days, 5:12, 1 user, load average: 0.43, 0.54, 0.57
Tasks: 227 total, 4 running, 223 sleeping, 0 stopped, 0 zombie
%Cpu(s): 35.2 us, 6.9 sy, 0.0 ni, 51.9 id, 0.0 wa, 0.0 hi, 6.0 si, 0.0 st
KiB Mem : 32947032 total, 29998788 free, 375068 used, 2573176 buff/cache
KiB Swap: 2097148 total, 1953380 free, 143768 used. 30803956 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11331 postgres 20 0 5976024 16036 13432 R 92.1 0.0 0:13.98 postgres
11163 postgres 20 0 5981000 16764 9088 S 1.3 0.1 0:01.24 postgres
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-23 08:16 soumik.bhattacharjee@kpn.com
parent: Adarsh Sharma <eddy.adarsh@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-23 08:16 UTC (permalink / raw)
To: eddy.adarsh@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
Additional information’s – another query with same table gets executed in Oracle in 0.0243 ms.
Table size
==============
SELECT pg_size_pretty( pg_total_relation_size('npcurren.NUM_AANGESLOTEN_NR') );
pg_size_pretty
----------------
3585 MB
(1 row)
- It’s a physical server, remote DB server in same country(Netherlands)
With the same table - 'npcurren.NUM_AANGESLOTEN_NR' another query it’s not even getting executed , it’s get “killed” in postgres please find the plan below
Query
===========
SELECT
i.*
FROM
npcurren.num_cps_instelling i,
npcurren.num_aangesloten_nr n
WHERE
n.fk_exploit_nop_int_oper_id = 'PTT'
AND i.telefoonnummer != n.anr_nummer_hoog
AND i.telefoonnummer != n.anr_nummer_laag;
Explain Plan
======================
"Nested Loop (cost=2068.47..1769226624.33 rows=101090505159 width=73)"
" Join Filter: (((i.telefoonnummer)::text <> (n.anr_nummer_hoog)::text) AND ((i.telefoonnummer)::text <> (n.anr_nummer_laag)::text))"
" -> Seq Scan on num_cps_instelling i (cost=0.00..12485.52 rows=539852 width=73)"
" -> Materialize (cost=2068.47..130399.99 rows=187256 width=22)"
" -> Bitmap Heap Scan on num_aangesloten_nr n (cost=2068.47..129463.71 rows=187256 width=22)"
" Recheck Cond: ((fk_exploit_nop_int_oper_id)::text = 'PTT'::text)"
" -> Bitmap Index Scan on anr_idx6 (cost=0.00..2021.65 rows=187256 width=0)"
" Index Cond: ((fk_exploit_nop_int_oper_id)::text = 'PTT'::text)"
From: Bhattacharjee, Soumik
Sent: Friday, August 23, 2019 10:02 AM
To: 'Adarsh Sharma' <eddy.adarsh@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: Postgres DB Slowness
Hi,
Please find below
"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046 width=113) (actual time=0.102..2226.525 rows=9649110 loops=1)"
"Planning Time: 2.050 ms"
"Execution Time: 2785.262 ms"
From: Adarsh Sharma <eddy.adarsh@gmail.com<mailto:eddy.adarsh@gmail.com>>
Sent: Thursday, August 22, 2019 7:12 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres DB Slowness
Can you please paste explain analyze SELECT *
FROM npcurren.num_aangesloten_nr output?
On Thu, Aug 22, 2019 at 8:57 PM <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>> wrote:
From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>>; pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: RE: Postgres DB Slowness
From: Ron <ronljohnsonjr@gmail.com><mailto:ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres DB Slowness
On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hello Members,
We have the below PostgreSQL database recently migrated from Oracle.
The postgres DB parameters are attached here.
# DB Version: 10
# OS Type: Linux
# Total Memory (RAM): 30 GB
#CPU
nproc --all
2
There is huge slowness in the database now with any queries.
Oracle - Select * from TABLENAME- in takes 0.009 milliseconds
PostgreSQL - Same query takes more than 2 minutes.
Have you validated that the Postgres server has the same indexes?
If so, did you ANALYZE all the tables?
Yes Ron, we migrated from Oracle as per same structure and indexes.
I did the analyze for 2-3 tables now as per my SQL query I wrote and also ran for one particular table below –
VACUUM (FULL,ANALYZE) table_name.
What does the query plan for that query look like?
What's the table definition?
Is the hardware comparable? (Might it be a SAN or VM issue?)
Table definition is attached here.
EXPLAIN SELECT *
FROM npcurren.num_aangesloten_nr;
"Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046 width=113)"
- Oracle 11gR2 was hosted on physical HP-UX server last updated 12 years back- legacy system
- Current postgres server- physical
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.6 (Maipo)
$ uname -a
Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
The select * from query takes all of the 2 CPU’s.
top - 17:24:33 up 42 days, 5:12, 1 user, load average: 0.43, 0.54, 0.57
Tasks: 227 total, 4 running, 223 sleeping, 0 stopped, 0 zombie
%Cpu(s): 35.2 us, 6.9 sy, 0.0 ni, 51.9 id, 0.0 wa, 0.0 hi, 6.0 si, 0.0 st
KiB Mem : 32947032 total, 29998788 free, 375068 used, 2573176 buff/cache
KiB Swap: 2097148 total, 1953380 free, 143768 used. 30803956 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11331 postgres 20 0 5976024 16036 13432 R 92.1 0.0 0:13.98 postgres
11163 postgres 20 0 5981000 16764 9088 S 1.3 0.1 0:01.24 postgres
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres DB Slowness
@ 2019-08-23 11:08 Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
parent: soumik.bhattacharjee@kpn.com
0 siblings, 1 reply; 17+ messages in thread
From: Juan José Santamaría Flecha @ 2019-08-23 11:08 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: eddy.adarsh@gmail.com; pgsql-admin@lists.postgresql.org
On Fri, Aug 23, 2019 at 10:17 AM <soumik.bhattacharjee@kpn.com> wrote:
>
> Query
> ===========
> SELECT
> i.*
> FROM
> npcurren.num_cps_instelling i,
> npcurren.num_aangesloten_nr n
> WHERE
> n.fk_exploit_nop_int_oper_id = 'PTT'
> AND i.telefoonnummer != n.anr_nummer_hoog
> AND i.telefoonnummer != n.anr_nummer_laag;
>
> Explain Plan
> ======================
> "Nested Loop (cost=2068.47..1769226624.33 rows=101090505159 width=73)"
>
This query looks wrong, resulting in a cartesian join.
As other people have pointed out, the execution times do not seem
reasonable, could you change the 'SELECT *' into a 'SELECT count(1)'
for the comparison?
Regards,
Juan José Santamaría Flecha
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres DB Slowness
@ 2019-08-23 11:35 legrand legrand <legrand_legrand@hotmail.com>
parent: soumik.bhattacharjee@kpn.com
3 siblings, 0 replies; 17+ messages in thread
From: legrand legrand @ 2019-08-23 11:35 UTC (permalink / raw)
To: pgsql-admin
Hi
Could you explain us how you test
Oracle - Select * from TABLENAME-
in takes 0.009 milliseconds
Is it with oracle sql developer ?
How long does it take with sql*plus ?
Note that sql developer only display first 50 or 100 rows ,
You should use the « count rows » button to get a better duration estimate.
Regards
PAscal
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Postgres DB Slowness
@ 2019-08-26 10:14 soumik.bhattacharjee@kpn.com
parent: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2019-08-26 10:14 UTC (permalink / raw)
To: juanjo.santamaria@gmail.com; +Cc: eddy.adarsh@gmail.com; pgsql-admin@lists.postgresql.org
-----Original Message-----
From: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
Sent: Friday, August 23, 2019 1:08 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: eddy.adarsh@gmail.com; pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness
On Fri, Aug 23, 2019 at 10:17 AM <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>> wrote:
>
> Query
> ===========
> SELECT
> i.*
> FROM
> npcurren.num_cps_instelling i,
> npcurren.num_aangesloten_nr n
> WHERE
> n.fk_exploit_nop_int_oper_id = 'PTT'
> AND i.telefoonnummer != n.anr_nummer_hoog
> AND i.telefoonnummer != n.anr_nummer_laag;
>
> Explain Plan
> ======================
> "Nested Loop (cost=2068.47..1769226624.33 rows=101090505159 width=73)"
>
This query looks wrong, resulting in a cartesian join.
As other people have pointed out, the execution times do not seem reasonable, could you change the 'SELECT *' into a 'SELECT count(1)'
for the comparison?
Regards,
Juan José Santamaría Flecha
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hi Juan,
The query plan is below -
numbes_test=# explain analyze select i.* from npcurren.NUM_CPS_INSTELLING i, npcurren.NUM_AANGESLOTEN_NR n where n.FK_EXPLOIT_NOP_INT_OPER_ID = 'PTT' and i.TELEFOONNUMMER != n.ANR_NUMMER_HOOG and i.TELEFOONNUMMER != n.ANR_NUMMER_LAAG;Â
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2071.86..1817248003.24 rows=103834572306 width=73) (actual time=68.512..28074371.506 rows=98046804138 loops=1)
Join Filter: (((i.telefoonnummer)::text <> (n.anr_nummer_hoog)::text) AND ((i.telefoonnummer)::text <> (n.anr_nummer_laag)::text))
Rows Removed by Join Filter: 36398
-> Seq Scan on num_cps_instelling i (cost=0.00..12485.52 rows=539852 width=73) (actual time=0.069..403.077 rows=539852 loops=1)
-> Materialize (cost=2071.86..130606.58 rows=192339 width=22) (actual time=0.002..16.490 rows=181618 loops=539852)
-> Bitmap Heap Scan on num_aangesloten_nr n (cost=2071.86..129644.88 rows=192339 width=22) (actual time=68.425..555.068 rows=181618 loops=1)
Recheck Cond: ((fk_exploit_nop_int_oper_id)::text = 'PTT'::text)
Heap Blocks: exact=51470
-> Bitmap Index Scan on anr_idx6 (cost=0.00..2023.78 rows=192339 width=0) (actual time=59.276..59.276 rows=181618 loops=1)
Index Cond: ((fk_exploit_nop_int_oper_id)::text = 'PTT'::text)
Planning time: 4.702 ms
Execution time: 31536903.127 ms
(12 rows)
Does this also looks bad for performance with Data types- as there is varchar in a number records field ? - The table DDL is attached
anr_nummer_laag character varying(20) COLLATE pg_catalog."default" NOT NULL,
anr_nummer_hoog character varying(20) COLLATE pg_catalog."default" NOT NULL,
Attachments:
Table_Def.txt (text/plain, 4.1K) download
CREATE TABLE npcurren.num_aangesloten_nr
(
anr_nummer_laag character varying(20) COLLATE pg_catalog."default" NOT NULL,
anr_nummer_hoog character varying(20) COLLATE pg_catalog."default" NOT NULL,
fk_eigenaar_nop_int_oper_id character varying(6) COLLATE pg_catalog."default",
fk_sta_id character varying(1) COLLATE pg_catalog."default",
fk_sac_id character varying(1) COLLATE pg_catalog."default",
fk_moeder_hst_spc_id character varying(4) COLLATE pg_catalog."default",
fk_moeder_cgb_code integer,
fk_exploit_nop_int_oper_id character varying(6) COLLATE pg_catalog."default",
fk_dienst_hst_spc_id character varying(4) COLLATE pg_catalog."default",
fk_dienst_cgb_code integer,
anr_soort_dienst character varying(2) COLLATE pg_catalog."default",
anr_roepnummer character varying(20) COLLATE pg_catalog."default",
anr_lri character varying(24) COLLATE pg_catalog."default",
anr_logisch_mutatiemoment timestamp without time zone,
anr_technisch_mutatiemoment timestamp without time zone NOT NULL,
anr_mutatie_gebruiker_id character varying(8) COLLATE pg_catalog."default" NOT NULL,
anr_netnummer character varying(4) COLLATE pg_catalog."default",
anr_heeft_cvi_dienst_jn character varying(1) COLLATE pg_catalog."default",
fk_num_voip_platform_id smallint,
anr_heeft_cvi_zm_dienst_yn character varying(1) COLLATE pg_catalog."default",
anr_cvi_kenmerk character varying(10) COLLATE pg_catalog."default",
anr_cvi_telco character varying(8) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT anr_prx PRIMARY KEY (anr_nummer_laag),
CONSTRAINT sys_c0012384 FOREIGN KEY (fk_sac_id)
REFERENCES npcurren.num_soort_actie (sac_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT sys_c0012385 FOREIGN KEY (fk_sta_id)
REFERENCES npcurren.num_status (sta_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT sys_c0012386 FOREIGN KEY (fk_num_voip_platform_id)
REFERENCES npcurren.num_voip_platform (platform_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE npcurren.num_aangesloten_nr
OWNER to postgres;
-- Index: anr_idx1
-- DROP INDEX npcurren.anr_idx1;
CREATE INDEX anr_idx1
ON npcurren.num_aangesloten_nr USING btree
(fk_moeder_hst_spc_id COLLATE pg_catalog."default", fk_moeder_cgb_code)
TABLESPACE pg_default;
-- Index: anr_idx2
-- DROP INDEX npcurren.anr_idx2;
CREATE INDEX anr_idx2
ON npcurren.num_aangesloten_nr USING btree
(fk_dienst_hst_spc_id COLLATE pg_catalog."default", fk_dienst_cgb_code)
TABLESPACE pg_default;
-- Index: anr_idx3
-- DROP INDEX npcurren.anr_idx3;
CREATE INDEX anr_idx3
ON npcurren.num_aangesloten_nr USING btree
(fk_sac_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx4
-- DROP INDEX npcurren.anr_idx4;
CREATE INDEX anr_idx4
ON npcurren.num_aangesloten_nr USING btree
(fk_sta_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx5
-- DROP INDEX npcurren.anr_idx5;
CREATE INDEX anr_idx5
ON npcurren.num_aangesloten_nr USING btree
(fk_eigenaar_nop_int_oper_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx6
-- DROP INDEX npcurren.anr_idx6;
CREATE INDEX anr_idx6
ON npcurren.num_aangesloten_nr USING btree
(fk_exploit_nop_int_oper_id COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx7
-- DROP INDEX npcurren.anr_idx7;
CREATE INDEX anr_idx7
ON npcurren.num_aangesloten_nr USING btree
(anr_nummer_hoog COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx8
-- DROP INDEX npcurren.anr_idx8;
CREATE INDEX anr_idx8
ON npcurren.num_aangesloten_nr USING btree
(anr_netnummer COLLATE pg_catalog."default")
TABLESPACE pg_default;
-- Index: anr_idx9
-- DROP INDEX npcurren.anr_idx9;
CREATE INDEX anr_idx9
ON npcurren.num_aangesloten_nr USING btree
(fk_num_voip_platform_id)
TABLESPACE pg_default;
end of thread, other threads:[~2019-08-26 10:14 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-08-22 14:04 Postgres DB Slowness soumik.bhattacharjee@kpn.com
2019-08-22 14:09 ` soumik.bhattacharjee@kpn.com
2019-08-22 14:25 ` Ron <ronljohnsonjr@gmail.com>
2019-08-22 14:33 ` Perry Chandler <Perry.Chandler@taxcalc.com>
2019-08-22 14:35 ` soumik.bhattacharjee@kpn.com
2019-08-22 14:46 ` Ron <ronljohnsonjr@gmail.com>
2019-08-22 15:04 ` soumik.bhattacharjee@kpn.com
2019-08-22 15:15 ` Holger Jakobs <holger@jakobs.com>
2019-08-22 15:25 ` soumik.bhattacharjee@kpn.com
2019-08-22 17:12 ` Adarsh Sharma <eddy.adarsh@gmail.com>
2019-08-23 08:01 ` soumik.bhattacharjee@kpn.com
2019-08-23 08:16 ` soumik.bhattacharjee@kpn.com
2019-08-23 11:08 ` Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
2019-08-26 10:14 ` soumik.bhattacharjee@kpn.com
2019-08-22 20:30 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2019-08-22 20:57 ` Jeff Janes <jeff.janes@gmail.com>
2019-08-23 11:35 ` legrand legrand <legrand_legrand@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox