I am trying to optimize and tune my server for fastest simple queries with highest connection to server possible. Basically, a SELECT item from table takes 30 minutes on a machine with SSD drives. The table has 900K entries and 12 columns. Using that SELECT query, I then have the ability to make about 500 simultaneous connections to the server before errors start to occur. So, I can live with 500, but the slow query gives me grief.
I have a GIST index for the table. I tried btree, but according to performance tests, GIST was faster. So I went with GIST.
The system drive and pgsql drive are separate. I can separate them further if need to be. Total ram is 56GB. I added 32GB of swap.
Is there anything I can do to speed up the SELECT statement?
Here is what I have:
[sysctl.conf]
net.ipv4.conf.default.rp_filter=1
net.ipv4.conf.all.rp_filter=1
net.ipv4.tcp_syncookies=1
net.ipv4.ip_forward=1
net.ipv6.conf.all.forwarding=1
net.ipv4.conf.all.accept_redirects=0
net.ipv6.conf.all.accept_redirects=0
net.ipv4.conf.all.accept_source_route=0
net.ipv6.conf.all.accept_source_route=0
net.ipv4.conf.all.log_martians=1
kernel.sysrq=0
kernel.shmmax=2147483999999
kernel.shmall=2097159999999
#32GBkernel.shmmax=17179869184
#32GBkernel.shmall=4194304
kernel.shmmni=999999999
kernel.shmmin=1
kernel.shmseg=10
semmsl, semmns, semopm, semmni kernel.sem=250 32000 100 128
fs.file-max=65536
kern.maxfiles=50000
kern.maxfilesperproc=50000
net.ipv4.ip_local_port_range=1024 65535
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_tw_reuse=1
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.ipv4.tcp_max_syn_backlog=4096
net.ipv4.tcp_syncookies=1
kernel.sched_migration_cost_ns=5000000
kernel.sched_migration_cost_ns=5000000
kernel.sched_autogroup_enabled=0
vm.swappiness=10
[postgresql.conf]
max_connections = 100000
max_files_per_process = 1000000
shared_buffers = 24GB
max_locks_per_transaction = 1000
effective_cache_size = 50GB
work_mem = 100MB
maintenance_work_mem = 2GB
log_min_duration_statement = 10000
checkpoint_completion_target = 0.9
wal_buffers = 32MB
default_statistics_target = 100
listen_addresses = '*'
port = 5432
ssl = off
wal_sync_method = fdatasync
synchronous_commit = on
fsync = off
wal_level = minimal
#client_min_messages = fatal
#log_min_messages = fatal
#log_min_error_statement = fatal
datestyle = 'iso, mdy'
debug_pretty_print = off
debug_print_parse = off
debug_print_plan = off
debug_print_rewritten = off
default_text_search_config = 'pg_catalog.english'
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexonlyscan = on
enable_indexscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
from_collapse_limit = 8
geqo = on
geqo_threshold = 12
log_checkpoints = off
log_connections = off
log_disconnections = off
log_duration = off
log_executor_stats = off
log_hostname = off
log_parser_stats = off
log_planner_stats = off
log_replication_commands = off
log_statement_stats = off
log_timezone = 'UTC'
max_wal_size = 1GB
min_wal_size = 80MB
shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen'
stats_temp_directory = 'pg_stat_tmp'
timezone = 'US/Pacific'
track_activities = on
track_counts = on
track_io_timing = off
Thanks in advance.
