1

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
  • See if an index on (submitter_id, id DESC) helps. – sticky bit Jan 27 at 21:21
  • Just to confirm so nothing is screwed, you're implying I do CREATE INDEX CONCURRENTLY link_id_submitter_id ON links_link (submitter_id, id DESC)? – Hassan Baig Jan 27 at 21:25
  • Yes, that looks good. – sticky bit Jan 27 at 21:27
  • Just imagine how you'd search for the result in that index -- traverse it to find the subtree for the submitter_id and then just pick the first node, as they're sorted by id DESC. What do you mean by undo? You can just drop it if you don't want it any more, if that's what you're asking (cannot be used anymore afterwards of course). – sticky bit Jan 27 at 21:35
  • That should be alright. – sticky bit Jan 27 at 21:42
2

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.

  • Yes, this is a very effective solution. The "DESC" is probably not necessary there, and if it isn't I think it is better to omit it. In most cases PostgreSQL can follow an index in either direction. Once he has an index on (submitter_id, id) he can probably drop the one on just (submitter_id), no point in maintaining an index that is redundant. – jjanes Jan 28 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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