Optimizing SELECT query consistently showing up in slow-log
The plan is not using the index on (which_photo_id) but the PK (id) index instead, so it has to read a very large part of the index (and all of it if there are less than 25 rows that match the filter). This takes about 4.4 seconds in the specific execution (and finds the 25 rows after reading and rejecting 19M rows):

-&gt; Index Scan Backward using links_photocomment_pkey on links_photocomment  
   (cost=0.57..2,819,246.22 rows=7,195 width=41)
   (actual time=555.830..4,929.154 rows=25 loops=1)

   Filter: (which_photo_id = 3115087)
   Rows Removed by Filter: 19100179


I would try these:

replacing the index on (which_photo_id) with an index on (which_photo_id, id).

rewrite the INNER join to a LEFT join (there is a FOREIGN KEY constraint that ensures that the two queries will produce the same results.)

rewrite with a subquery (derived table or CTE) moving the WHERE filter inside) in order to first get the 25 ids (hopefully with an index only scan) and then join the other 2 tables.


Query (with derived table):
SELECT &quot;links_photocomment&quot;.&quot;abuse&quot;,
       &quot;links_photocomment&quot;.&quot;text&quot;,
       &quot;links_photocomment&quot;.&quot;id&quot;,
       &quot;links_photocomment&quot;.&quot;submitted_by_id&quot;,
       &quot;links_photocomment&quot;.&quot;submitted_on&quot;,
       &quot;auth_user&quot;.&quot;username&quot;,
       &quot;links_userprofile&quot;.&quot;score&quot;
FROM   
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       ) AS lim
       INNER JOIN &quot;links_photocomment&quot;
           ON ( &quot;links_photocomment&quot;.&quot;id&quot; = lim.id )
       LEFT OUTER JOIN &quot;auth_user&quot;
           ON ( &quot;links_photocomment&quot;.&quot;submitted_by_id&quot; = &quot;auth_user&quot;.&quot;id&quot; )
       LEFT OUTER JOIN &quot;links_userprofile&quot;
           ON ( &quot;auth_user&quot;.&quot;id&quot; = &quot;links_userprofile&quot;.&quot;user_id&quot; )
ORDER  BY lim.id DESC
LIMIT  25;

Query (with CTE):
WITH lim AS
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       )
SELECT &quot;links_photocomment&quot;.&quot;abuse&quot;,
       &quot;links_photocomment&quot;.&quot;text&quot;,
       &quot;links_photocomment&quot;.&quot;id&quot;,
       &quot;links_photocomment&quot;.&quot;submitted_by_id&quot;,
       &quot;links_photocomment&quot;.&quot;submitted_on&quot;,
       &quot;auth_user&quot;.&quot;username&quot;,
       &quot;links_userprofile&quot;.&quot;score&quot;
FROM   
       lim
       INNER JOIN &quot;links_photocomment&quot;
           ON ( &quot;links_photocomment&quot;.&quot;id&quot; = lim.id )
       LEFT OUTER JOIN &quot;auth_user&quot;
           ON ( &quot;links_photocomment&quot;.&quot;submitted_by_id&quot; = &quot;auth_user&quot;.&quot;id&quot; )
       LEFT OUTER JOIN &quot;links_userprofile&quot;
           ON ( &quot;auth_user&quot;.&quot;id&quot; = &quot;links_userprofile&quot;.&quot;user_id&quot; )
ORDER  BY lim.id DESC
LIMIT  25;