5 ms·
10,000x Speedup for Postgres Queries: How to Make a Smart Optimizer More Stupid
- rgbgraph 3y agoA DB article that you can actually learn something from. Lovely
- paulryanrogers 3y agoImpressive. 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.
- lomereiter 3y agoWe 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.
- alyandon 3y agoPostgreSQL 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 https://github.com/ossc-db/pg_hint_plan exists so that will probably become a standard thing I deploy.
- Shorel 3y agoFamously, 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.
- alyandon 3y agoI'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.
- johnthescott 3y agoyep. 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.
- johnthescott 3y agoi prefer the pg stance on no query hints. you can always install the extension.
- davidpfarrell 3y agoAm 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;
- apavlo 3y agoIt should be `item.added_rev_id = 3`. I fixed the typo. Not sure how it got mangled. Thanks!