Re: Optimization via explicit JOINs

On Fri, 9 Mar 2001, David Olbersen wrote:

> On Fri, 9 Mar 2001, Stephan Szabo wrote:
> 
> -> Hmm, what were the two queries anyway?
> 
> The "slower" query
> ----------------------------
> SELECT
>   to_char( p.insertion_time, 'HH:MI AM MM/DD' ) as time_in,
>   s.name                as title,
>   a.name                as artist,
>   s.length              as length
> FROM
>   playlist p,
>   songs    s,
>   artists  a
> WHERE
>   p.waiting   = TRUE          AND
>   p.song_id   = s.song_id     AND
>   s.artist_id = a.artist_id
> ORDER BY p.item_id
> 
> The "faster" query
> ----------------------------
> SELECT
>   to_char( p.insertion_time, 'HH:MI AM MM/DD' ) as time_in,
>   s.name                as title,
>   s.length              as length,
>   a.name                as artist
> FROM
>   playlist p JOIN songs s USING (song_id),
>   artists  a
> WHERE
>   p.waiting   = TRUE          AND
>   p.song_id   = s.song_id     AND
>   s.artist_id = a.artist_id
> ORDER BY p.item_id;
> 
> Notice how the only difference is in the FROM clause?

Yeah.  It's getting the same plan, just a slightly different number
of estimated rows (14 and 1) from the join of p to s.
As a question, how many rows does
select * from playlist p join songs s using (song_id) where
p.waiting=TRUE;
actually result in?