On Sat, Nov 2, 2019 at 10:57 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > The idea that I've been thinking about is to not generate disabled > Paths in the first place, thus not only fixing the problem but saving > some cycles. While this seems easy enough for "optional" paths, > we have to reserve the ability to generate certain path types regardless, > if there's no other way to implement the query. This is a bit of a > stumbling block :-(. At the base relation level, we could do something > like generating seqscan last, and only if no other path has been > successfully generated.
Continuing my investigation into this rather old thread, I did a rather primitive implementation of this idea, for baserels only, and discovered that it caused a small number of planner failures running the regression tests. Here is a slightly simplified example: CREATE TABLE strtest (n text, t text); CREATE INDEX strtest_n_idx ON strtest (n); SET enable_seqscan=false; EXPLAIN SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n; With the patch, I get: ERROR: could not devise a query plan for the given query The problem here is that it's perfectly possible to generate a valid path for s1 -- and likewise for s2, since it's the same underlying relation -- while respecting the enable_seqscan=false constraint. However, all such paths are parameterized by the other of the two relations, which means that if we do that, we can't plan the join, because we need an unparameterized path for at least one of the two sides in order to build a nested loop join, which is the only way to satisfy the parameterization on the other side. Now, you could try to fix this by deciding that planning for a baserel hasn't really succeeded unless we got at least one *unparameterized* path for that baserel. I haven't tried that, but I presume that if you do, it fixes the above example, because now there will be a last-ditch sequential scan on both sides and so this example will behave as expected. But if you do that, then in other cases, that sequential scan is going to get picked even when it isn't strictly necessary to do so, just because some plan that uses it looks better on cost. Presumably that problem can in turn be fixed by deciding that we also need to keep disable_cost around (or the separate disable-counter idea that we were discussing recently in another branch of this thread), but that's arguably missing the point of this exercise. Another idea is to remove the ERROR mentioned above from set_cheapest() and just allow planning to continue even if some relations end up with no paths. (This would necessitate finding and fixing any code that could be confused by a pathless relation.) Then, if you get to the top of the plan tree and you have no paths there, redo the join search discarding the constraints (or maybe just some of the constraints, e.g. allow sequential scans and nested loops, or something). Conceptually, I like this idea a lot, but I think there are a few problems. One is that I'm not quite sure how to find all the code that would need to be adjusted to make it work, though the header comment for standard_join_search() seems like it's got some helpful tips. A second is that it's another version of the disable_cost = infinity problem: once you find that you can't generate a path while enforcing all of the restrictions, you just disregard the restrictions completely, instead of discarding them only to the extent necessary. I have a feeling that's not going to be very appealing. Now, I suppose it might be that even if we can't remove disable_cost, something along these lines is still worth doing, just to save CPU cycles. You could for example try planning with only non-disabled stuff and then do it over again with everything if that doesn't work out, still keeping disable_cost around so that you avoid disabled nodes where you can. But I'm kind of hoping that I'm missing something and there's some approach that could both kill disable_cost and save some cycles at the same time. If (any of) you have an idea, I'd love to hear it! -- Robert Haas EDB: http://www.enterprisedb.com
