Re: JOIN issues (Left vs Right for sorting), and "Nested Loop" problem - Mailing list pgsql-general

From Tom Lane
Subject Re: JOIN issues (Left vs Right for sorting), and "Nested Loop" problem
Date
Msg-id 21752.1188666572@sss.pgh.pa.us
Whole thread Raw
In response to Re: JOIN issues (Left vs Right for sorting), and "Nested Loop" problem  (Alban Hertroys <alban@magproductions.nl>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Alban Hertroys
Date:
Subject: Re: Obtaining random rows from a result set
Next
From: Tom Lane
Date:
Subject: Re: WAL Archiving problem