10,000x Speedup for Postgres Queries: How to Make a Smart Optimizer More Stupid

  PostgreSQL famously does not support plan hints 

I really wish the PostgreSQL core team would acknowledge that their stance on that hurts more than helps. Even Oracle with decades of engineering behind it doesn't get execution plans correct 100% of the time and provides a way to tune query execution via hints.

However, TIL that https://github.com/ossc-db/pg_hint_plan exists so that will probably become a standard thing I deploy.

Impressive. I would have first tried (perhaps naively) to put the unlimited query in a sub query and the LIMIT on the outer. Still, nice to know there are ways, however awkward.

A DB article that you can actually learn something from. Lovely

We ran into a similar issue once, and addressed it by changing the sorting column from id to created_at which has the same ordering but doesn't have an index on it. Good to know Postgres optimizer can be tricked even easier.

Am I the only one confused by `3)` in the example query?

        SELECT item.id
          FROM item
          JOIN item_set ON (item_set.id = item.item_set_id)
         WHERE item_set.name = 'ABC'
           AND item.added_rev_id  3)
         ORDER BY item.id ASC LIMIT 10;