We have a postgreSQL table with ~5B rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.

The problem generally manifests on an ORDER BY LIMIT clause (a common pattern in Django pagination) where the LIMIT is some relatively small subset of the results matched by the index. An extreme example is this:

Limit  (cost=0.58..4632.03 rows=1 width=28)
   ->  Index Scan Backward using mcqueen_base_imagemeta2_pkey on mcqueen_base_imagemeta2  (cost=0.58..364597074.75 rows=78722 width=28)
         Filter: (image_id = ANY ('{123, ...}'::bigint[]))

Limit  (cost=7585.92..7585.93 rows=2 width=28)
   ->  Sort  (cost=7585.92..7782.73 rows=78722 width=28)
         Sort Key: id DESC
         ->  Index Scan using mcqueen_base_imagemeta2_image_id_616fe89c on mcqueen_base_imagemeta2  (cost=0.58..6798.70 rows=78722 width=28)
               Index Cond: (image_id = ANY ('{123, ...}'::bigint[]))

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.

The problem generally manifests on an ORDER BY .. LIMIT .. clause (a common pattern in Django pagination) where the LIMIT is some relatively small subset of the results matched by the index. An extreme example is this:

Limit  (cost=0.58..4632.03 rows=1 width=28)
   ->  Index Scan Backward using mcqueen_base_imagemeta2_pkey on mcqueen_base_imagemeta2  (cost=0.58..364597074.75 rows=78722 width=28)
         Filter: (image_id = ANY ('{123, ...}'::bigint[]))

Limit  (cost=7585.92..7585.93 rows=2 width=28)
   ->  Sort  (cost=7585.92..7782.73 rows=78722 width=28)
         Sort Key: id DESC
         ->  Index Scan using mcqueen_base_imagemeta2_image_id_616fe89c on mcqueen_base_imagemeta2  (cost=0.58..6798.70 rows=78722 width=28)
               Index Cond: (image_id = ANY ('{123, ...}'::bigint[]))