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.