On Sat, 23 Mar 2019 at 04:12, Tom Lane <t...@sss.pgh.pa.us> wrote: > The reason why I'm skeptical about LIMIT with a plan of the form > append-some-sorts-together is that there are going to be large > discontinuities in the cost-vs-number-of-rows-returned graph, > ie, every time you finish one child plan and start the next one, > there'll be a hiccup while the sort happens. This is something > that our plan cost approximation (startup cost followed by linear > output up to total cost) can't even represent. Sticking a > LIMIT on top of that is certainly not going to lead to any useful > estimate of the actual cost, meaning that if you get a correct > plan choice it'll just be by luck, and if you don't there'll be > nothing to be done about it.
Thanks for explaining. I see where you're coming from now. I think this point would carry more weight if using the Append instead of the MergeAppend were worse in some cases as we could end up using an inferior plan accidentally. However, that's not the case. The Append plan should always perform better both for startup and pulling a single row all the way to pulling the final row. The underlying subplans are the same in each case, but Append has the additional saving of not having to determine to perform a sort on the top row from each subpath. I also think that cost-vs-number-of-rows-returned is not any worse than a SeqScan where the required rows are unevenly distributed throughout the table. In fact, the SeqScan case is much worse as we could end up choosing that over an index scan, which could be significantly better, but as mentioned above, and benchmarked in the initial post of this thread, Append always wins over MergeAppend, so I don't quite understand your reasoning here. I could understand it if Append needed the sorts but MergeAppend did not, but they both need the sorts if there's not a path that already provides the required ordering. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
