pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Slow Multi-joins performance [DEVELOPERS attn please]
4+ messages / 4 participants
[nested] [flat]
* Re: Slow Multi-joins performance [DEVELOPERS attn please]
@ 2002-09-09 15:24 Richard Huxton <dev@archonet.com>
2002-09-09 16:37 ` Re: Slow Multi-joins performance [DEVELOPERS attn please] Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-09 20:26 ` Re: Slow Multi-joins performance [DEVELOPERS attn please] Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Richard Huxton @ 2002-09-09 15:24 UTC (permalink / raw)
To: jlparkinson@bigpond.com; pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
On Friday 06 Sep 2002 11:59 am, jlparkinson@bigpond.com wrote:
> Has the performance for queries with lots of joins (more than 5) been
> improved in v7.2 ?
>
> I'm using 7.0.3 on Redhat 7.1 on a K2-6 500 CPU, and it is about 3 times
> slower than MySQL, or Access on windoze platform :-(
>
> I tried different command-line optimisations, and got the best results (on
> other data) with "-fm -fh -fs", but still not to the expected results of a
> fraction of a second to return the data.
> Changing the sort buffer options, etc, had little effect.
>
> To prove the point (albeit a trivial example), here is some test tables,
> that take over 3 seconds to retrieve one row of data from tables containing
> only one row of data each.
(Tom - sorry to cc: you on this, but I'm not sure if I'm talking rubbish here)
Interesting - I get something similar here. If I rewrite the view with
explicit joins as below:
SELECT t.id, a.name AS ta, b.name AS tb ... FROM t JOIN a ON t.ta=a.id JOIN b
ON t.tb=b.id ...
it returns instantly. Running an EXPLAIN ANALYSE, both have similar query
plans with twelve nested joins and 13 seq scans (as you'd expect for tables
with 1 row each). The only apparent difference is the order of the seq scans.
The best bit is the
Total runtime: 4.32 msec (original)
Total runtime: 5.32 msec (explicit JOINs)
Which says to me that your form is fine. Testing says otherwise, so there must
be some element of the query that is not being accounted for in EXPLAIN
ANALYSE. Your log shows the genetic algorithm (geqo_main line) kicking in
because it sees a complex query and it could be that this is the problem -
PostgreSQL takes a look at the 13-way join and thinks it's going to be very
expensive. If you had a genuinely complex query, the time to analyse options
would be a benefit, but here I'm guessing it's not. Perhaps try it with
increasing amounts of data and more restrictions and see if performance stays
constant.
- Richard Huxton
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow Multi-joins performance [DEVELOPERS attn please]
2002-09-09 15:24 Re: Slow Multi-joins performance [DEVELOPERS attn please] Richard Huxton <dev@archonet.com>
@ 2002-09-09 16:37 ` Ross J. Reedstrom <reedstrm@rice.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Ross J. Reedstrom @ 2002-09-09 16:37 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: jlparkinson@bigpond.com, pgsql-sql@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Richard -
Your analysis of this looks right on, to me. With current code,
if you put in explicit JOINS, the table get joined in that order,
no questions. By specifying an all JOIN version, you've made the
optimizers job very easy: only one plan to consider.
Your point about realistic data and complexity of queries is a good one.
There has been some recent work on doing something to cache query plans,
so if the same query gets run a lot, you only pay the planning cost a
few times. Not sure hoe much of that code (if any) made it into 7.3.
As an aside, the EXPLAIN text shows row estimates of 10, when we _know_
the tables have 1 row each, so VACUUM ANALYZE needs to be run. Doing
so (on a 7.1.2 datbse, BTW) cuts the measured execution time in half
(though not to instantanious, since planning still occurs) Letting the
planner/optimzer know as much as possible is almost always a good thing.
Ross
On Mon, Sep 09, 2002 at 04:24:08PM +0100, Richard Huxton wrote:
> On Friday 06 Sep 2002 11:59 am, jlparkinson@bigpond.com wrote:
<snip artificial 13 way join example>
> Interesting - I get something similar here. If I rewrite the view with
> explicit joins as below:
>
> SELECT t.id, a.name AS ta, b.name AS tb ... FROM t JOIN a ON t.ta=a.id JOIN b
> ON t.tb=b.id ...
>
> it returns instantly. Running an EXPLAIN ANALYSE, both have similar query
> plans with twelve nested joins and 13 seq scans (as you'd expect for tables
> with 1 row each). The only apparent difference is the order of the seq scans.
> The best bit is the
>
> Total runtime: 4.32 msec (original)
> Total runtime: 5.32 msec (explicit JOINs)
>
> Which says to me that your form is fine. Testing says otherwise, so there must
> be some element of the query that is not being accounted for in EXPLAIN
> ANALYSE. Your log shows the genetic algorithm (geqo_main line) kicking in
> because it sees a complex query and it could be that this is the problem -
> PostgreSQL takes a look at the 13-way join and thinks it's going to be very
> expensive. If you had a genuinely complex query, the time to analyse options
> would be a benefit, but here I'm guessing it's not. Perhaps try it with
> increasing amounts of data and more restrictions and see if performance stays
> constant.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow Multi-joins performance [DEVELOPERS attn please]
2002-09-09 15:24 Re: Slow Multi-joins performance [DEVELOPERS attn please] Richard Huxton <dev@archonet.com>
@ 2002-09-09 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-15 21:34 ` Re: Slow Multi-joins performance [DEVELOPERS attn please] jlparkinson@bigpond.com <jlparkinson@bigpond.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-09-09 20:26 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: jlparkinson@bigpond.com; pgsql-sql
Richard Huxton <dev@archonet.com> writes:
> Which says to me that your form is fine. Testing says otherwise, so there must
> be some element of the query that is not being accounted for in EXPLAIN
> ANALYSE.
To wit, planning time. EXPLAIN ANALYZE only counts execution time.
And planning time on a 13-way join is going to be nontrivial ---
especially compared to execution against trivial-size tables.
You can turn on some query stats logging (I forget the SET-variable
names) to get a feeling for the relative costs of planning and
execution; but usually planning drops into the noise once you start
looking at production-sized cases.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow Multi-joins performance [DEVELOPERS attn please]
2002-09-09 15:24 Re: Slow Multi-joins performance [DEVELOPERS attn please] Richard Huxton <dev@archonet.com>
2002-09-09 20:26 ` Re: Slow Multi-joins performance [DEVELOPERS attn please] Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-09-15 21:34 ` jlparkinson@bigpond.com <jlparkinson@bigpond.com>
0 siblings, 0 replies; 4+ messages in thread
From: jlparkinson@bigpond.com @ 2002-09-15 21:34 UTC (permalink / raw)
To: pgsql-sql
On Tue, 10 Sep 2002 06:26, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Which says to me that your form is fine. Testing says otherwise, so there
> > must be some element of the query that is not being accounted for in
> > EXPLAIN ANALYSE.
>
> To wit, planning time. EXPLAIN ANALYZE only counts execution time.
>
> And planning time on a 13-way join is going to be nontrivial ---
> especially compared to execution against trivial-size tables.
>
> You can turn on some query stats logging (I forget the SET-variable
> names) to get a feeling for the relative costs of planning and
> execution; but usually planning drops into the noise once you start
> looking at production-sized cases.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Does this mean that v7.3 will be about three (3) times faster than v7.1 so
that it matches MySQL and MS-ACCESS?
Or is Postgresql slow for multi-joins with small amounts of data, and only
come into its own when there is more than a million records involved
(suggested in a previous reply, that using caching of query plans will
increase speed)?
regards,
James
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-09-15 21:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-09 15:24 Re: Slow Multi-joins performance [DEVELOPERS attn please] Richard Huxton <dev@archonet.com>
2002-09-09 16:37 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-09 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-15 21:34 ` jlparkinson@bigpond.com <jlparkinson@bigpond.com>
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