Re: add_path optimization

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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 <cycle 5> [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]