Thread: Optimizer Path Candidates difference in 9.1.3 and 9.2 beta1
Hi, hackers
I modified the code in add_path() a bit so that all the query path candidates inside pathlist will not be removed and all new path will be added into the pathlist, thus all path candidates are kept in pathlist. I then tested a four-relation query. In 9.1.3, I can see thousands of candidates in the final RelOptInfo, and some of them are even busy trees. But in 9.2 beta1 which I forked from github, there are no such busy trees and only about 50 join path in total, which should match the requirement of System R algo. Is there any modification regarding the system R algo in the new release? And something wrong in algo in 9.1.3?
Thanks
Best Regards
Best Regards
Huang Qi Victor
Computer Science of National University of Singapore
Qi Huang <huangqiyx@hotmail.com> writes:
> Hi, hackers I modified the code in add_path() a bit so that all the query path candidates inside pathlist will not
beremoved and all new path will be added into the pathlist, thus all path candidates are kept in pathlist. I then
testeda four-relation query. In 9.1.3, I can see thousands of candidates in the final RelOptInfo, and some of them are
evenbusy trees. But in 9.2 beta1 which I forked from github, there are no such busy trees and only about 50 join path
intotal, which should match the requirement of System R algo. Is there any modification regarding the system R algo in
thenew release? And something wrong in algo in 9.1.3? Thanks
[ shrug... ] When you're not showing us exactly what you did, it's hard
to answer that for sure. But there is some prefiltering logic in
joinpath.c that you might have to lobotomize too if you want to keep
known-inferior join paths.
regards, tom lane
> [ shrug... ] When you're not showing us exactly what you did, it's hard
> to answer that for sure. But there is some prefiltering logic in
> joinpath.c that you might have to lobotomize too if you want to keep
> known-inferior join paths.
>
> regards, tom lane
Thanks, Tom.
Below is what I did for the code in add_path(). I commented out the section of remove_old, and also make sure always accept_new. Then at make_one_rel(), after calling "rel = make_rel_from_joinlist(root, joinlist); ", I set "pprint(rel)" in the next line. Checking the RelOptInfo printed out in logfile, I can see some bushy trees, in 9.1.3.
Best Regards
267 >---/*
268 >--- * Loop to check proposed new path against old paths. Note it is possible
269 >--- * for more than one old path to be tossed out because new_path dominates
270 >--- * it.
271 >--- *
272 >--- * We can't use foreach here because the loop body may delete the current
273 >--- * list cell.
274 >--- */
275 >---p1_prev = NULL;
276 >---for (p1 = list_head(parent_rel->pathlist); p1 != NULL; p1 = p1_next)
277 >---{
...................................
338
339 >--->---/*
340 >--->--- * Remove current element from pathlist if dominated by new.
341 >--->--- */
342 //>->---if (remove_old)
343 //>->---{
344 //>->--->---parent_rel->pathlist = list_delete_cell(parent_rel->pathlist,
345 //>->--->--->--->--->--->--->--->--->--->--->--->---p1, p1_prev);
346
347 >--->--->---/*
348 >--->--->--- * Delete the data pointed-to by the deleted cell, if possible
349 >--->--->--- */
350 //>->--->---if (!IsA(old_path, IndexPath))
351 //>->--->--->---pfree(old_path);
352 >--->--->---/* p1_prev does not advance */
353 //>->---}
354 //>->---else
355 //>->---{
356 >--->--->---/* new belongs after this old path if it has cost >= old's */
357 >--->--->---if (costcmp >= 0)
358 >--->--->--->---insert_after = p1;
359 >--->--->---/* p1_prev advances */
360 >--->--->---p1_prev = p1;
361 //>->---}
362
363 >--->---/*
364 >--->--- * If we found an old path that dominates new_path, we can quit
365 >--->--- * scanning the pathlist; we will not add new_path, and we assume
366 >--->--- * new_path cannot dominate any other elements of the pathlist.
367 >--->--- */
368 >--->---if (!accept_new)
369 >--->--->---break;
370 >---}
371
372 //>-if (accept_new)
373 //>-{
374 >--->---/* Accept the new path: insert it at proper place in pathlist */
375 >--->---if (insert_after)
376 >--->--->---lappend_cell(parent_rel->pathlist, insert_after, new_path);
377 >--->---else
378 >--->--->---parent_rel->pathlist = lcons(new_path, parent_rel->pathlist);
379 //>-}
380 //>-else
381 //>-{
382 >--->---/* Reject and recycle the new path */
383 //>->---if (!IsA(new_path, IndexPath))
384 //>->--->---pfree(new_path);
385 //>-}
Best Regards
Huang Qi Victor
Computer Science of National University of Singapore