Hacker News new | past | comments | ask | show | jobs | submit login
10,000x Speedup for Postgres Queries: How to Make a Smart Optimizer More Stupid (ottertune.com)
71 points by angry__panda on May 16, 2023 | hide | past | favorite | 10 comments



  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.


Famously, they didn't support Windows for the first decade of their existence, and that did cost them a tremendous amount of market share against MySQL.

By their technical merits, PostgreSQL should be the most used DB.


I've followed the project since the 6.x days and I am still impressed that they managed (with the help of some really sharp win32 people) to get their fork-per-client model working on Windows.


yep. the windoze process model very much not like the unix process model. pg has been happily unix since 6.x days.

also, certain key players in pg community have threatened to quit if pg went the threaded execution model. getting threading correct is very, very difficult.

took many years (with much help from win experts) to get the port correct AND maintain the unix process-per-query model on unix.


i prefer the pg stance on no query hints. you can always install the extension.


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;


It should be `item.added_rev_id = 3`. I fixed the typo. Not sure how it got mangled. Thanks!




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: