3 ms·

Super in-depth analysis, thanks to the author for writing it. Past the recommendation of counting based on an indexed column, I wonder if this should really be

Super in-depth analysis, thanks to the author for writing it. Past the recommendation of counting based on an indexed column, I wonder if this should really be user's concern. This paragraph especially triggers a "this should be fixed upstream" feeling in me: > A word of warning. When work_mem is high enough to hold the whole relation PostgreSQL will choose HashAggregate even when an index exists. Paradoxically, giving the database more memory resources can lead to a worse plan. You can force the index-only scan by setting SET enable_hashagg=false; but remember to set it true again afterward or other query plans will get messed up. But worst case scenario, this article will be useful until this is fixed, so thanks again :)

barrkel 10y ago
If you think "needs upstream fix", then no database is suitable for use, because they all choose poor plans in many edge cases. This is why most databases have extensions that let you hint or force index use (not Postgres, however; a mistake with mitigation), or support parenthesising your joins to force an evaluation order (this is an indirect way of forcing index use or join order, a mitigation), etc. Query planning is something where a poor choice can have serious performance ramifications, because n is usually much larger than in most programs. Analyzing the algorithmic complexity of a piece of SQL takes some experience and experimentation, and with different table stats the query planner may make different decisions. It can be worthwhile limiting the planner's discretion to get more predictable performance. (I work on a product where many of the features can be expressed in terms of relational algebra. Often, both the best performing and quickest to write and test implementation logic is a bunch of SQL, and not the kind of CRUD that is easily wrapped with an ORM. What would make my life easier is a SQL linter that, given a model of costs, would prevent people writing queries that scale only linearly with specific table sizes. I've accumulated sufficient intuition that I could do this for MySQL at this point.)
- oelmekki 10y ago
  Optimizing queries manually is fine by me, what troubled me was more the fact that it was recommended to change a configuration setting on the fly, which sounded like asking for problems regarding concurrent requests. But actually, I've just made a test, and it appears changing this setting only impacts the current connection, so provided it's toggled back after the request, this should not be a problem.
  - pavlik_enemy 10y ago
    Though I wonder how it would work with pg-bouncer.
- pavlik_enemy 10y ago
  You can disable certain scan types in Postgres and that's something I had to do when no matter what it reported incorrect stats.
honkhonkpants 10y ago
Problems of this kind are the reason I always wanted a dbms to offer a direct plan API instead of a sql parser. It's dumb to fiddle with the statement until you get the desired plan. You should be able to just call for that plan.

barrkel 10y ago

If you think "needs upstream fix", then no database is suitable for use, because they all choose poor plans in many edge cases. This is why most databases have extensions that let you hint or force index use (not Postgres, however; a mistake with mitigation), or support parenthesising your joins to force an evaluation order (this is an indirect way of forcing index use or join order, a mitigation), etc. Query planning is something where a poor choice can have serious performance ramifications, because n is usually much larger than in most programs. Analyzing the algorithmic complexity of a piece of SQL takes some experience and experimentation, and with different table stats the query planner may make different decisions. It can be worthwhile limiting the planner's discretion to get more predictable performance. (I work on a product where many of the features can be expressed in terms of relational algebra. Often, both the best performing and quickest to write and test implementation logic is a bunch of SQL, and not the kind of CRUD that is easily wrapped with an ORM. What would make my life easier is a SQL linter that, given a model of costs, would prevent people writing queries that scale only linearly with specific table sizes. I've accumulated sufficient intuition that I could do this for MySQL at this point.)

oelmekki 10y ago
Optimizing queries manually is fine by me, what troubled me was more the fact that it was recommended to change a configuration setting on the fly, which sounded like asking for problems regarding concurrent requests. But actually, I've just made a test, and it appears changing this setting only impacts the current connection, so provided it's toggled back after the request, this should not be a problem.
- pavlik_enemy 10y ago
  Though I wonder how it would work with pg-bouncer.
pavlik_enemy 10y ago
You can disable certain scan types in Postgres and that's something I had to do when no matter what it reported incorrect stats.

honkhonkpants 10y ago

Problems of this kind are the reason I always wanted a dbms to offer a direct plan API instead of a sql parser. It's dumb to fiddle with the statement until you get the desired plan. You should be able to just call for that plan.