Re: add_path optimization - Mailing list pgsql-hackers

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> [ test case ]

It looks to me like the reason for the planning time difference is that
this query contains four NOT EXISTS subqueries, which 8.3 was not very
smart about but 8.4 has converted into antijoins.  That gives it more
flexibility to consider different join orders, which means more paths to
sort through, so it takes longer.  But in principle you are more likely
to get a good plan.  (You didn't say anything about the actual runtimes
--- I'd be interested to know about the runtimes and the quality of the
rowcount estimates in both cases.)

So as far as the fact that planning is slower is concerned, it's pretty
much "nothing to see here, move along".  I notice though that the
profile shows add_path is eating even more run-time percentage wise
than before, because it's getting called more.  (It's up from about
14% to 21%, counting subroutines --- see below.)  So there might be an
argument for installing Robert's optimization or something like it in
8.4 to buy some of that back, rather than waiting for 8.5.
        regards, tom lane

8.3:
               0.00    0.00    3700/2893200     set_rel_pathlist <cycle 5> [327]               0.00    0.00
4500/2893200    create_index_paths <cycle 5> [132]               0.51    0.17 2885000/2893200     add_paths_to_joinrel
<cycle5> [14]
 
[16]    14.7    0.51    0.18 2893200         add_path [16]               0.13    0.00 6401100/10760100
compare_pathkeys[29]               0.00    0.02  454600/621400      list_delete_cell [112]               0.01    0.00
453400/4243582    AllocSetFree [48]               0.01    0.00  453400/4242980     pfree [66]               0.00
0.00  85700/512901      lcons [98]               0.00    0.00  208700/1934900     compare_path_costs [196]
 

8.4:
               0.00    0.00    4100/10605500     set_rel_pathlist <cycle 8> [200]               0.00    0.00
4300/10605500    create_index_paths <cycle 8> [207]               2.20    0.57 10597100/10605500
add_paths_to_joinrel<cycle 8> [14]
 
[16]    21.7    2.20    0.57 10605500         add_path [16]               0.45    0.00 30231600/47490100
compare_pathkeys[24]               0.02    0.05 1584000/1909000     list_delete_cell [81]               0.03    0.00
1582800/13590386    AllocSetFree [46]               0.01    0.00 1014900/10462300     compare_path_costs [53]
   0.01    0.00 1582800/13589684     pfree [62]               0.00    0.00  169400/833901      lcons [108]