3 ms·

I care about the total amount of time until I get to do real work with the data returned. Subsecond query plans for ad-hoc workloads are pointless for me if I t

by thom 9y ago

I care about the total amount of time until I get to do real work with the data returned. Subsecond query plans for ad-hoc workloads are pointless for me if I then wait 10 hours for results. Here's a concrete example: a view with a GROUP BY. Give it a concrete ID to filter by, and it will push it down to the underlying table and return quick results. I can then script a loop to do this to get the full dataset for a list of IDs. However, if I supply a `WHERE id IN (...)` or a JOIN, the query plan will be totally different and will take forever. This is dumb, and this is with up-to-date statistics etc. I'm happy to accept I'm probably not the target market, but just having the option to leave the query planner running for minutes instead of milliseconds would be great (if it indeed has work to _do_ in those minutes).

fatbird 9y ago
That was going to be my next question: is there any tunability to the query planner? Obviously you're going to be more tolerant of planner overhead than someone maintaining the maintaining the backend for a big CRUD site. ETA: some quick googling suggests no, since most tuning documentation is on the initial query or the indexing.
- pgaddict 9y ago
  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.

by thom 9y ago

fatbird 9y ago

That was going to be my next question: is there any tunability to the query planner? Obviously you're going to be more tolerant of planner overhead than someone maintaining the maintaining the backend for a big CRUD site. ETA: some quick googling suggests no, since most tuning documentation is on the initial query or the indexing.

pgaddict 9y ago
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.