Postgres - Index not taken into account

The cause of the bad plan choice is a bad estimate: PostgreSQL thinks that 15576676 rows match your condition, while in reality 22 rows do. Consequently, it thinks that a sequential scan will be the most efficient access method.

To fix that, you have to see to improve the estimates. Here is what you can do:

1. Run an

   ANALYZE inbox;
   

   If that is enough to improve the situation, you have to see that auto-analyze runs more often, typically by lowering the scale factor:

   ALTER TABLE inbox SET (autovacuumanalyzescalefactor = 0.01);
   

2. If it is a table with a high change rate, there is also the possibility that autovacuum could not keep up. See if the following makes PostgreSQL choose the correct plan:

   VACUUM (ANALYZE) inbox;
   

   If yes, perhaps autovacuum runs to seldom or to slow. To improve that, you would have to reduce autovacuumvacuumscalefactor or autovacuumvacuumcostdelay on the table.

3. If that is not enough, you should check if one of the two conditions is estimated badly, or if it is the combination of both that is the problem. Run

   EXPLAIN (ANALYZE) SELECT 1 FROM inbox WHERE status = 'Failed';
   

   and

   EXPLAIN (ANALYZE) SELECT 1 FROM inbox WHERE retrycount < 3;
   

   Compare the estimated row count and the actual row count for both.

   If one of the conditions is the problem, you'd have to increase the statistics target for that column:

   ALTER TABLE inbox ALTER col SET STATISTICS 1000;
   ANALYZE inbox;
   

   That should improve the estimate and make PostgreSQL choose the correct plan.

4. If the individual conditions are estimated correctly, and it is the combination of the two that is the problem, extended statistics might help:

   CREATE STATISTICS inboxstatusretrycountstats
      ON status, retrycount FROM inbox;
   ANALYZE inbox;
   

   Then PostgreSQL will be able to account for dependencies between the values of that column.