Re: add_path optimization

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Oh, dear. If this turns out to be my bug Tom will kick my ass!

Hmm ... one of the things that struck me as odd was that it was doing a
merge join on just the countyNo, which is presumably very far from
unique. Testing the query here with Kevin's schema but no data, I get

-> Merge Anti Join (cost=0.00..102.51 rows=233 width=34)
Merge Cond: ((("CD"."countyNo")::smallint = ("CD2"."countyNo")::smallint) AND (("CD"."caseNo")::text = ("CD2"."caseNo")::text))
Join Filter: (("CD2"."dispoDate")::date > ("CD"."dispoDate")::date)
-> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD" (cost=0.00..49.50 rows=350 width=38)
-> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD2" (cost=0.00..49.50 rows=350 width=38)

ie it's using the first two columns of the pkey not only the first
column as merge key (and not arbitrarily using two different indexes to
accomplish the same scan, which is another weird thing about that plan).
There's no visible reason for it not to have done that in Kevin's test,
unless there's something wrong with your patch.

There might be more than one bug here though. The other question is
why it wants to do this join first at all, and I'm not convinced that
add_path could be at fault for that. I'm suspecting that the logic
that considers join order restrictions for antijoins might be overly
restrictive.

regards, tom lane