Slow query performance of simple SELECT query (Postgresql 9.6)

Question

In a app called 'Links', users post links of interesting content they've discovered recently (and other vote on the said posts).

These posted links are saved in a links_link table in my postgresql 9.6.5 DB.

One innocuous looking SELECT query on the links_link table is consistently showing up in slow_log. It's taking longer than 500ms, and is ~10X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

LOG: duration: 8648.676 ms statement:

SELECT "links_link"."id",
       "links_link"."description",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."net_votes"
FROM   "links_link"
WHERE  "links_link"."submitter_id" = 811645
ORDER  BY "links_link"."id" DESC
LIMIT  1  

See the the explain analyze results: https://explain.depesz.com/s/Xp5v

The query ends up filtering 14,331,127 rows according to that!

What I've tried:

My gut feel is that Postgres bases this query plan on misleading statistics. Thus, I've run VACUUM ANALYZE on the said table. However this hasn't changed anything.

Being an accidental DBA of sorts, I'm looking for some quick expert guidance on the subject. Thanks in advance and apologies for the noob question (if it is).

Edit:

Here's the entire output for \d links_link:

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_latest_reply_id_idx" btree (latest_reply_id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE

Answer 1

I could imagine an index on links_link (submitter_id, id DESC) being of great help here.

CREATE INDEX CONCURRENTLY link_id_submitter_id
                          ON links_link (submitter_id, id DESC);

It can be traversed to quickly find the subtree for submitter_id = 811645. Then the first node can be picked instantly to get the first record ordered by id DESC.