Thread: Optimizer Path Candidates difference in 9.1.3 and 9.2 beta1

Optimizer Path Candidates difference in 9.1.3 and 9.2 beta1

From
Qi Huang
Date:
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
Huang Qi Victor
Computer Science of National University of Singapore

Re: Optimizer Path Candidates difference in 9.1.3 and 9.2 beta1

From
Tom Lane
Date:
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


Re: Optimizer Path Candidates difference in 9.1.3 and 9.2 beta1

From
Qi Huang
Date:

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