Another thought... since you are limiting yourself to a maximum number of paths at any given time, if you're willing to take the full hit for maintaining the full N=30 (or whatever) paths, instead of simply eliminating worse paths with identical nodes, allow yourself to continue finding more paths to bring your total back up to 30.
For instance, in the example with N=4, and N1-R would be eliminated, you could take the '5th best' path to maintain your count of 4. So, you'd be taking the best N paths that have a unique set of nodes. I don't know if that will give enough bang for your buck, as the extra lower paths might rarely yield a final better result. Marc ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp [d...@sqlite.org] Sent: Tuesday, April 30, 2013 6:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query optimizer bug? On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen <mlal...@outsitenetworks.com>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. 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: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik > Sent: Tuesday, April 30, 2013 5:34 PM > To: sqlite-users@sqlite.org > 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 > sqlite-users@sqlite.org > 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
