Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: jlparkinson@bigpond.com, pgsql-sql@postgresql.org
Subject: Re: Slow Multi-joins performance [DEVELOPERS attn please]
Date: Mon, 9 Sep 2002 16:24:08 +0100
User-Agent: KMail/1.4.1
References: <02090620595601.02695@localhost>
In-Reply-To: <02090620595601.02695@localhost>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209091624.08766.dev@archonet.com>

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 containi=
ng
> only one row of data each.

(Tom - sorry to cc: you on this, but I'm not sure if I'm talking rubbish he=
re)

Interesting - I get something similar here. If I rewrite the view with=20
explicit joins as below:

SELECT t.id, a.name AS ta, b.name AS tb ... FROM t JOIN a ON t.ta=3Da.id JO=
IN b=20
ON t.tb=3Db.id ...

it returns instantly. Running an EXPLAIN ANALYSE, both have similar query=
=20
plans with twelve nested joins and 13 seq scans (as you'd expect for tables=
=20
with 1 row each). The only apparent difference is the order of the seq scan=
s.=20
The best bit is the=20

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 m=
ust=20
be some element of the query that is not being accounted for in EXPLAIN=20
ANALYSE. Your log shows the genetic algorithm (geqo_main line) kicking in=
=20
because it sees a complex query and it could be that this is the problem -=
=20
PostgreSQL takes a look at the 13-way join and thinks it's going to be very=
=20
expensive. If you had a genuinely complex query, the time to analyse option=
s=20
would be a benefit, but here I'm guessing it's not. Perhaps try it with=20
increasing amounts of data and more restrictions and see if performance sta=
ys=20
constant.

- Richard Huxton