Alban Hertroys <alban@magproductions.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=1loops=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