sql - Postgresql simple query very slow response time -

i have simple database using build out json elasticsearch. i'm person connected database, , using specific task. anyway, hitting tables on database incredibly slow, slow take months build out elasticsearch index working on. i've looked @ articles regarding postgresql tuning, etc, , nothing i'm changing fixing issue. query doing, can see below in explain analyze portion, simple query, no joins or else, , it's still slow. i'm trying figure out can speed up, because see no reason slow.

here's relevant information can think of, if more needed, can add.

system specs

[root@pgdb ~]# free -m              total       used       free     shared    buffers     cached mem:         61444      39416      22027        458          8      38531 -/+ buffers/cache:        876      60568 swap:            0          0          0   [root@pgdb ~]# cat /proc/cpuinfo processor   : 0 vendor_id   : genuineintel cpu family  : 6 model       : 62 model name  : intel(r) xeon(r) cpu e5-2670 v2 @ 2.50ghz 

all tables

cotitsk=# \d+                           list of relations  schema |       name       | type  |  owner   |  size   | description --------+------------------+-------+----------+---------+-------------  public | company          | table | postgres | 1838 mb |  public | company_industry | table | postgres | 1621 mb |  public | company_title    | table | postgres | 3837 mb |  public | industry         | table | postgres | 224 kb  |  public | industry_skill   | table | postgres | 446 mb  |  public | industry_title   | table | postgres | 1229 mb |  public | interest         | table | postgres | 344 mb  |  public | skill            | table | postgres | 438 mb  |  public | skill_skill      | table | postgres | 21 gb   |  public | title            | table | postgres | 1841 mb |  public | title_interest   | table | postgres | 2799 mb |  public | title_skill      | table | postgres | 27 gb   |  public | title_title      | table | postgres | 11 gb   | (13 rows) 

schema of table being queried

cotitsk=# \d+ skill_skill                       table "public.skill_skill"   column   |  type  | modifiers | storage | stats target | description -----------+--------+-----------+---------+--------------+-------------  skill1_id | bigint | not null  | plain   |              |  skill2_id | bigint | not null  | plain   |              |  count     | bigint | not null  | plain   |              | foreign-key constraints:     "skill_skill_skill1_fk" foreign key (skill1_id) references skill(skill_id)     "skill_skill_skill2_fk" foreign key (skill2_id) references skill(skill_id) 

approximate row count of table being queried

cotitsk=# select reltuples::bigint estimate pg_class relname='skill_skill';  estimate -----------  435104320 (1 row) 

explain analyze

cotitsk=# explain analyze select * skill_skill skill1_id = '2701941' order count desc limit 1000;                                                                 query plan ------------------------------------------------------------------------------------------------------------------------------------------  limit  (cost=8228458.83..8228461.33 rows=1000 width=24) (actual time=37163.205..37163.600 rows=1000 loops=1)    ->  sort  (cost=8228458.83..8229292.78 rows=333580 width=24) (actual time=37163.203..37163.394 rows=1000 loops=1)          sort key: count desc          sort method: top-n heapsort  memory: 127kb          ->  seq scan on skill_skill  (cost=0.00..8210169.00 rows=333580 width=24) (actual time=14021.081..37128.913 rows=210902 loops=1)                filter: (skill1_id = '2701941'::bigint)                rows removed filter: 434893298  planning time: 0.062 ms  execution time: 37163.747 ms (9 rows) 

postgresql.conf

max_connections = 1000 shared_buffers = 16gb work_mem = 100mb maintenance_work_mem = 1gb dynamic_shared_memory_type = posix wal_buffers = 16mb effective_cache_size = 48gb logging_collector = on log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_timezone = 'utc'