1

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

1 Answer 1

3

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.

2
  • Thanks for the guidance. I'm assuming I can do this concurrently as well, i.e. CREATE INDEX CONCURRENTLY ON links_publicreply (direct_reply_id);? Nov 11, 2019 at 15:25
  • 1
    Yes, of course. Nov 11, 2019 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.