On Tue, Apr 30, 2013 at 6:20 PM, Richard Hipp <[email protected]> wrote:
> > > On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen < > [email protected]> wrote: > >> In looking at the draft plan... am I right in assuming that at any 'stop' >> you can eliminate paths which have consumed the identical set of nodes but >> are more expensive? >> > > Yes. Good idea. I have updated the document accordingly. > Upon further reflection, the answer is really "maybe". If one path is a candidate for optimizing out the ORDER BY clause but the other is not, then you still one to keep around the path that is the ORDER BY candidate even if it is not the lowest cost. In the example below, if there had been an ORDER BY clause that was such that N1-R might be able to satisfy the ORDER BY clause without sorting, then we would want to keep N1-R around even though R-N1 has a lower cost at this step. But that sort of thing happens rarely, and pruning by merging equivalent paths will normally be a big win. > > Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is > found with N==10 instead of N==21. > > >> >> For instance, at stop 2, the draft shows: >> >> R-N1 (cost: 7.03) >> N1-R (cost: 7.31) >> R-N2 (cost: 9.08) >> N2-R (cost: 9.08) >> >> Since the first two consume both R and N1, will N1-R ever be a better >> path than R-N1? If not, then it can be removed from the set, reducing the >> amount of work later. Similarly with R-N2 and N2-R. Since they consume >> the same nodes and have identical costs, does it matter which one is used? >> Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z? >> >> Maybe I'm over simplifying? >> >> Marc >> >> -----Original Message----- >> From: [email protected] [mailto: >> [email protected]] On Behalf Of Igor Tandetnik >> Sent: Tuesday, April 30, 2013 5:34 PM >> To: [email protected] >> Subject: Re: [sqlite] Query optimizer bug? >> >> On 4/30/2013 5:26 PM, Ryan Johnson wrote: >> > Being able to force certain access methods (use *this* index, not that >> > one) would be helpful, though (does "+" do that or just suggest it?). >> >> Unary plus turns a simple column reference (for which an index can be >> used) into an expression (which cannot be indexed). This may be used to >> suppress the choice of a particular index, at which point the query planner >> is likely to choose a different index, often rearranging the joins. So the >> unary plus does often influence the query plan, but in a rather roundabout >> way. >> -- >> Igor Tandetnik >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> This email and any attachments are only for use by the intended >> recipient(s) and may contain legally privileged, confidential, proprietary >> or otherwise private information. Any unauthorized use, reproduction, >> dissemination, distribution or other disclosure of the contents of this >> e-mail or its attachments is strictly prohibited. If you have received this >> email in error, please notify the sender immediately and delete the >> original. >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > [email protected] > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
