Optimizer Path Candidates difference in 9.1.3 and 9.2 beta1

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 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

[ 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.

267 >---/*268 >--- * Loop to check proposed new path against old paths. Note it is possible269 >--- * for more than one old path to be tossed out because new_path dominates270 >--- * it.271 >--- *272 >--- * We can't use foreach here because the loop body may delete the current273 >--- * list cell.274 >--- */275 >---p1_prev = NULL;276 >---for (p1 = list_head(parent_rel->pathlist); p1 != NULL; p1 = p1_next)277 >---{ ...................................338339 >--->---/* 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 RegardsHuang Qi VictorComputer Science of National University of Singapore