pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Optimization via explicit JOINs
10+ messages / 3 participants
[nested] [flat]

* Optimization via explicit JOINs
@ 2001-03-09 22:00 David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 10+ messages in thread

From: David Olbersen @ 2001-03-09 22:00 UTC (permalink / raw)
  To: pgsql-sql

Greetings,
  I've been toying aroudn with postgres 7.1beta5's ability to control the
  planner via explicitely JOINing tables. I then (just for giggles) compare the
  difference in the EXPLAIN results.

  I'm no super-mondo-DBA or anything, but in my two attempts so far, the numbers
  I get out of EXPLAIN have been about 1/2 as small.

  Below are two EXPLAIN results, am I correct in reading that one is indeed
  "twice as fast" as the other? I say twice as fast because the top-most cost in
  the first query is 58.62, but in the second one it's only 32.09. Am I reading
  this correctly?

-- First EXPLAIN --
Sort  (cost=58.62..58.62 rows=14 width=60)
  ->  Nested Loop  (cost=0.00..58.35 rows=14)
    ->  Nested Loop  (cost=0.00..29.99 rows=14)
      ->  Seq Scan on playlist p  (cost=0.00..1.61 rows=14)
      ->  Index Scan using songs_pkey on songs s (cost=0.00..2.01 rows=1)
    ->  Index Scan using artists_pkey on artists a (cost=0.00..2.01 rows=1)


-- Second EXPLAIN --
Sort  (cost=32.09..32.09 rows=1)
  ->  Nested Loop  (cost=0.00..32.08 rows=1)
    ->  Nested Loop  (cost=0.00..30.06 rows=1)
      ->  Seq Scan on playlist p  (cost=0.00..1.61 rows=14)
      ->  Index Scan using songs_pkey on songs s (cost=0.00..2.02 rows=1)
    ->  Index Scan using artists_pkey on artists a  (cost=0.00..2.01 rows=1)

-- Dave




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
@ 2001-03-09 22:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  0 siblings, 1 reply; 10+ messages in thread

From: Stephan Szabo @ 2001-03-09 22:13 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql


On Fri, 9 Mar 2001, David Olbersen wrote:

> Greetings,
>   I've been toying aroudn with postgres 7.1beta5's ability to control the
>   planner via explicitely JOINing tables. I then (just for giggles) compare the
>   difference in the EXPLAIN results.
> 
>   I'm no super-mondo-DBA or anything, but in my two attempts so far, the numbers
>   I get out of EXPLAIN have been about 1/2 as small.
> 
>   Below are two EXPLAIN results, am I correct in reading that one is indeed
>   "twice as fast" as the other? I say twice as fast because the top-most cost in
>   the first query is 58.62, but in the second one it's only 32.09. Am I reading
>   this correctly?

Not entirely.  Those are only estimates, so they don't entirely line up
with reality.  Also, I notice the first estimates 14 rows and the second
1,  which is probably why the estimate is higher.  In practice it probably
won't be significantly different.




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-09 22:47   ` David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 10+ messages in thread

From: David Olbersen @ 2001-03-09 22:47 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

On Fri, 9 Mar 2001, Stephan Szabo wrote:

->Not entirely.  Those are only estimates, so they don't entirely line up
->with reality.  Also, I notice the first estimates 14 rows and the second
->1,  which is probably why the estimate is higher.  In practice it probably
->won't be significantly different.

So really I'm just getting back estimations of cost and rows returned?
Incidentally, both queries returned the same data set, that's a Good Thing (tm).

-- Dave




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
@ 2001-03-09 23:10     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 23:54       ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  0 siblings, 1 reply; 10+ messages in thread

From: Stephan Szabo @ 2001-03-09 23:10 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql


> On Fri, 9 Mar 2001, Stephan Szabo wrote:
> 
> ->Not entirely.  Those are only estimates, so they don't entirely line up
> ->with reality.  Also, I notice the first estimates 14 rows and the second
> ->1,  which is probably why the estimate is higher.  In practice it probably
> ->won't be significantly different.
> 
> So really I'm just getting back estimations of cost and rows returned?
> Incidentally, both queries returned the same data set, that's a Good Thing (tm).

Yeah, explain is mostly ofr showing what it's going to do and a little bit
of why it thinks it's a good idea.  Hmm, what were the two queries anyway?





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-09 23:54       ` David Olbersen <dave@slickness.org>
  2001-03-10 01:56         ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 10+ messages in thread

From: David Olbersen @ 2001-03-09 23:54 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

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?
-- Dave




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 23:54       ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
@ 2001-03-10 01:56         ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-10 02:04           ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  0 siblings, 1 reply; 10+ messages in thread

From: Stephan Szabo @ 2001-03-10 01:56 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql


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?




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 23:54       ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-10 01:56         ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-10 02:04           ` David Olbersen <dave@slickness.org>
  2001-03-10 02:17             ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-10 02:18             ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 2 replies; 10+ messages in thread

From: David Olbersen @ 2001-03-10 02:04 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

On Fri, 9 Mar 2001, Stephan Szabo wrote:

->As a question, how many rows does
->select * from playlist p join songs s using (song_id) where
->p.waiting=TRUE;
->actually result in?

Well it depends. Most of the time that playlist table is "empty" (no rows where
waiting = TRUE), however users can (in a round about way) insert into that
table, so that there could be anywhere from 10, to 2,342, to more.

Why do you ask?

(The reason those plans chose 14 was because, at the time, there were 14 rows in
playlist)

-- Dave




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 23:54       ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-10 01:56         ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-10 02:04           ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
@ 2001-03-10 02:17             ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Stephan Szabo @ 2001-03-10 02:17 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql


Darn.  Well, one of the queries picked that 1 row was going to survive
the nested loop step and the other said 14.  I was wondering which one
was closer to being correct at that time.

On Fri, 9 Mar 2001, David Olbersen wrote:

> On Fri, 9 Mar 2001, Stephan Szabo wrote:
> 
> ->As a question, how many rows does
> ->select * from playlist p join songs s using (song_id) where
> ->p.waiting=TRUE;
> ->actually result in?
> 
> Well it depends. Most of the time that playlist table is "empty" (no rows where
> waiting = TRUE), however users can (in a round about way) insert into that
> table, so that there could be anywhere from 10, to 2,342, to more.
> 
> Why do you ask?
> 
> (The reason those plans chose 14 was because, at the time, there were 14 rows in
> playlist)




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 23:54       ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-10 01:56         ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-10 02:04           ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
@ 2001-03-10 02:18             ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-10 03:03               ` Re: Optimization via explicit JOINs Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Stephan Szabo @ 2001-03-10 02:18 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql


Actually, just thought of something else.  If you remove
the probably redundant p.song_id=s.song_id from the second 
query (since the join ... using should do that) does it
change the explain output?

On Fri, 9 Mar 2001, David Olbersen wrote:

> On Fri, 9 Mar 2001, Stephan Szabo wrote:
> 
> ->As a question, how many rows does
> ->select * from playlist p join songs s using (song_id) where
> ->p.waiting=TRUE;
> ->actually result in?
> 
> Well it depends. Most of the time that playlist table is "empty" (no rows where
> waiting = TRUE), however users can (in a round about way) insert into that
> table, so that there could be anywhere from 10, to 2,342, to more.
> 
> Why do you ask?
> 
> (The reason those plans chose 14 was because, at the time, there were 14 rows in
> playlist)




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Optimization via explicit JOINs
  2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 22:13 ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 22:47   ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-09 23:10     ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-09 23:54       ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-10 01:56         ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-10 02:04           ` Re: Optimization via explicit JOINs David Olbersen <dave@slickness.org>
  2001-03-10 02:18             ` Re: Optimization via explicit JOINs Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-10 03:03               ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2001-03-10 03:03 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: David Olbersen <dave@slickness.org>; pgsql-sql

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Actually, just thought of something else.  If you remove
> the probably redundant p.song_id=s.song_id from the second 
> query (since the join ... using should do that) does it
> change the explain output?

I was just about to point that out.  The WHERE clause *is* redundant
with the JOIN ... USING clause, but the planner will not recognize that,
and accordingly will multiply the estimated selectivity of the two
clauses together.  So the output row count for the JOIN form is
misleadingly small.  If you remove the redundant WHERE clause then you
should get identical planning estimates for both forms of the query.

The planner does actually recognize and discard duplicate qualifiers
that appear in the same place (eg, WHERE p.song_id=s.song_id AND
p.song_id=s.song_id) but it's not so smart about qualifiers that are
in different parts of the query...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2001-03-10 03:03 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-09 22:00 Optimization via explicit JOINs David Olbersen <dave@slickness.org>
2001-03-09 22:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-09 22:47   ` David Olbersen <dave@slickness.org>
2001-03-09 23:10     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-09 23:54       ` David Olbersen <dave@slickness.org>
2001-03-10 01:56         ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-10 02:04           ` David Olbersen <dave@slickness.org>
2001-03-10 02:17             ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-10 02:18             ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-10 03:03               ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox