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?