4 ms·

This is interesting. When you see these 'obvious to the eye' opportunities, do you stop and treat them as an exercise in generalizing the optimization for a qu

This is interesting. When you see these 'obvious to the eye' opportunities, do you stop and treat them as an exercise in generalizing the optimization for a query planner? I'm curious how much query planners have to make tradeoffs between effective optimizations and not overloading the analysis phase.

anarazel 9y ago
> I'm curious how much query planners have to make tradeoffs between effective optimizations and not overloading the analysis phase. In case of PostgreSQL: constantly. Pretty much every planner improvement has to argue that it's not likely to unduly increase plan time.
- kuschku 9y ago
  What about pre-compiling plans? With server-side prepared statements it would make sense to use entirely different planning time tradeoffs.
  - anarazel 9y ago
    It's not that simple. Server side prepared statements are used to great effect in OLTPish scenarios too. A serviceable approach would be to perform additional optimizations based on the query's cost - but that's hard to do without incurring a complete replan, because of the bottom up dynamic programming approach of a good chunk of the planning.
    - kuschku 9y ago
      Well, I’d love to see a way to set such a query planning cost target. There’s a few statements which I never change, but run thousands of times a day, and which are extremely expensive.
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.

anarazel 9y ago

> I'm curious how much query planners have to make tradeoffs between effective optimizations and not overloading the analysis phase. In case of PostgreSQL: constantly. Pretty much every planner improvement has to argue that it's not likely to unduly increase plan time.

kuschku 9y ago
What about pre-compiling plans? With server-side prepared statements it would make sense to use entirely different planning time tradeoffs.
- anarazel 9y ago
  It's not that simple. Server side prepared statements are used to great effect in OLTPish scenarios too. A serviceable approach would be to perform additional optimizations based on the query's cost - but that's hard to do without incurring a complete replan, because of the bottom up dynamic programming approach of a good chunk of the planning.
  - kuschku 9y ago
    Well, I’d love to see a way to set such a query planning cost target. There’s a few statements which I never change, but run thousands of times a day, and which are extremely expensive.

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.