Re: JOIN issues (Left vs Right for sorting), and "Nested Loop" problem

Alban Hertroys <alban(at)magproductions(dot)nl> writes:
> I am kind of surprised that the planner doesn't understand that a
> foreign key with a unique constraint (which a primary key is) means
> there is a 0..1 to 1 relationship with the target table.

Hm? It correctly estimated that it'd get one row out for each index
probe:

-> Index Scan using tradecount_pkey on tradecount (cost=0.00..8.45 rows=1 width=16) (actual time=0.006..0.008 rows=1 loops=386)
Index Cond: (trades.id = tradecount.id)

I don't think there's anything wrong with this plan at all, at least for
queries that select only a few hundred rows in trades. It would switch
to a different plan if a large fraction of the tables had to be joined,
but for joining a small fraction it's hard to beat nested indexscans.

(Of course, I'm assuming that this is a small fraction --- we don't
actually know the total sizes of these tables...)

regards, tom lane