0

I have a reddit-type app called 'Links' where 1) users post interesting hyperlinks, and 2) other users publicly respond to such postings. The former is defined as links_link table in my postgresql 9.3.10 DB, the latter is links_publicreply.

I've noticed that deletion is quite fast across most of the tables in the DB of this web app.

However, links_publicreply is problematic. E.g., I just deleted 238 rows from that table, and waited for ~20 mins for it to complete. This isn't an exception; it's been more like the rule since over a year now.

Now confessedly, it's a big table (~75M rows). The hardware it's operating on has 16 cores and 120 GB memory. I've been monitoring the server's performance - there's no bottleneck there, far from it.

Look at the the explain analyze results here: https://explain.depesz.com/s/ATwE It seems time is eaten up by a sequential scan.

Moreover here's the output of\d links_publicreply:

                                      Table "public.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
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "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_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    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

Is there any way I could have performed a query like this differently? I need to perform maintenance on this table - but the times are exorbitant. Please advise on all options (if any) I have here.


In case it matters, also adding the output for \d links_link here:

                                         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_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
0

1 Answer 1

1

The sequential scan is taking only 23 seconds. The key is to look at the full text of the plan, which shows the missing time:

Trigger for constraint publicreplyposter_link_fkey: time=766392.766 calls=238

(explain.depesz.com doesn't do such a great job with marking up the plans from non-select queries)

So, it looks like you are missing a create index on links_link (latest_reply_id).

That means every row you delete from links_publicreply has to do a full table scan on links_link to make sure it is not referencing that row.

5
  • Okay, just so that I don't fall into a rookie hole, have a few follow up questions. When I do create index on links_link (latest_reply_id);, would I be able to do it on my live environment without ill effects? Or are table locks and such I'll have to worry about? And are there any other considerations before I do this? Commented Sep 26, 2017 at 18:30
  • It is likely to lock the table against data changes for the duration of the index build. Reads should still be able to occur. There are some situations where reads will also be locked out, for example if another process tries to add a column to the table while the index is being built, the add column will block on the index, and then reads will block on the add column. If you use create index concurrently, then data changes will not be blocked, but the 2nd situation will still cause problems.
    – jjanes
    Commented Sep 26, 2017 at 19:28
  • Thanks for the heads up. Looks like the best solution is to actually do this during down time. From your experience, is this a relatively fast operation? Or does building indexes like this take a long time? Trying to ascertain how big a maintenance window I'll need. Commented Sep 26, 2017 at 19:33
  • And hey, isn't latest_reply_id already an index? It's a foreign key, and Django ORM auto creates indexes for foreign keys (that's the ORM I used to create this table). I've added a description of links_link in the question for you to see. Commented Sep 26, 2017 at 21:12
  • Your \d of the links_link describe doesn't show in index on latest_reply_id, just id and submitter_id. The target of a fk has to have an index, PostgreSQL enforces that. The referencing side of the fk should usually have an index, but PostgreSQL doesn't enforce that. Sorry, I don't know enough about Django to tell if Django is supposed to enforce that or not, but in this case it apparently didn't do so.
    – jjanes
    Commented Sep 27, 2017 at 0:31

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