3 ms·

Sure there are ways to affect the planner. For example one of the expensive steps is exploring the possible join tree - deciding in what order the tables will

Sure there are ways to affect the planner. For example one of the expensive steps is exploring the possible join tree - deciding in what order the tables will be joined (and then using which join algorithm). That's inherently exponential, because for N tables there are N! orderings. By default PostgreSQL will only do exhaustive search for up to 8 tables, and then switch to GEQO (genetic algorithm), but you can increase join_collapse_limit (and possibly also from_collapse_limit) to increase the threshold. Another thing is you may make the statistics more detailed/accurate, by increasing `default_statistics_target` - by default it's 100, so we have histograms with 100 buckets in histograms and up to 100 most common values, giving us ~1% resolution. But you can increase it up to 10000, to get more accurate estimates. It may help, but of course it makes ANALYZE and planning more expensive. And so on ... But in general, we don't want too many low-level knobs - we prefer to make the planner smarter in general. What you can do fairly easily is to replace the whole planner, and tweak it in various ways - that's why we have the hook/callback system, after all.

thom 9y ago
My default statistics target is 10000. I have turned GEQO off entirely, although I rarely hit the threshold at which it's relevant. But there's still no way of telling the planner that I am sat, in a psql session, doing an ad-hoc query with a deadline. The fact that there are situations where I have to disable sequential scans entirely proves to me that Postgres doesn't care about performance for interactive use. Simple SELECT count(*)s could save weeks of peoples lives but there's no way to tell the planner that you don't mind leaving it running over a coffee break.
- anarazel 9y ago
  Please report some of these cases to the list. We're much more likely to fix things that we hear are practical problems than the ones that we know theoretically exist, but are much more likely waste of plan time for everyone, not to speak of development and maintenance overhead. I find "disable sequential scans entirely proves to me that Postgres doesn't care about performance for interactive use." in combination with not reporting the issue a bit contradictory. We care about stuff we get diagnosable reports for.

thom 9y ago

My default statistics target is 10000. I have turned GEQO off entirely, although I rarely hit the threshold at which it's relevant. But there's still no way of telling the planner that I am sat, in a psql session, doing an ad-hoc query with a deadline. The fact that there are situations where I have to disable sequential scans entirely proves to me that Postgres doesn't care about performance for interactive use. Simple SELECT count(*)s could save weeks of peoples lives but there's no way to tell the planner that you don't mind leaving it running over a coffee break.

anarazel 9y ago
Please report some of these cases to the list. We're much more likely to fix things that we hear are practical problems than the ones that we know theoretically exist, but are much more likely waste of plan time for everyone, not to speak of development and maintenance overhead. I find "disable sequential scans entirely proves to me that Postgres doesn't care about performance for interactive use." in combination with not reporting the issue a bit contradictory. We care about stuff we get diagnosable reports for.