Speeding up very slow UPDATE query (Postgresql 9.6)

Question

In a app called Links, users post interesting content they've discovered recently and others can comment on these posts.

There's also an additional functionality, one that allows users to leave a comment on a comment.

All comments (and comments on comments) are saved in a table called links_publicreply in my postgresql 9.6.5 DB. This table contains a self-referential foreign key to support comments on comments. It's a big table, with nearly 200M rows.

One UPDATE query on the links_publicreply table is consistently showing up in slow_log. It's taking longer than 5000ms, and is ~100X slower than what I'm experiencing in most other postgresql operations.

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

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

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

According to that, the Seq Scan ends up filtering 47,535,365 rows and is the source of slowness.

---

What do I do to reduce this exorbitant execution time?

Being an accidental DBA, I'm not an expert on the subject. My gut feel is that I'm filtering on the self-referential foreign key, so that should already be an index (thus optimized for lookups)? I'm a bit stumped here.

---

Addition:

Here's the entire output for \d links_publicreply:

            Column             |           Type           |                           Modifiers                            
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     | 
 direct_reply_tgt_text_prefix  | text                     | 
 direct_reply_tgt_text_postfix | text                     | 
 direct_reply_id               | integer                  | 
 level                         | integer                  | 
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Answer 1

You should create a B-tree index:

CREATE INDEX ON links_publicreply (direct_reply_id);

Note: your index links_publicreply_answer_to_id is useless and should be dropped.