Re: Strange query planner behavior

Qi Huang <huangqiyx(at)hotmail(dot)com> writes:
> Hi, hackers (I'm doing another project related to Postgres besides the gSOC, so this one is not for TABLESAMPLE) I have a query as follows :select *from affiliation a, author_aff_history his, author auwhere a.aff_name = his.aff_nameand his.person_id = au.person_id;
> I trace the RelOptInfo *rel in make_one_rel. In the pathlist, however, I find there are two duplicate paths(the latter two, both start with NESTPATH, and NEST on (b 2) and (b1) first, then with (b 3)). And there is another path that I think should be the optimal join tree(left-deep and no cartesian product), but I can't find it in the pathlist. The rel structure is attached in this email, the duplicate are in line 644 and 1321 respectively. I'm not sure whether I should ask this in the hacker list or other list, but just try here first.Why is that so? Any suggestion?

The paths starting at lines 644 and 1321 don't seem remotely duplicate
to me. They do have the same inner path (an indexscan on relation 3
using the index with oid 16451) but the outer paths are very different,
a hash join and nestloop respectively. The reason the planner has kept
both of those is that one dominates on startup cost while the other
dominates on total cost. Neither of them look like cartesian products
either.

Dunno about the path you were expecting to see; probably it got
discarded on the basis of not looking competitive cost-wise. You
might try setting a breakpoint at add_path to see what happened to it.

regards, tom lane