From: Richard Huxton <dev@archonet.com>
To: jlparkinson@bigpond.com
To: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Slow Multi-joins performance [DEVELOPERS attn please]
Date: Mon, 9 Sep 2002 16:24:08 +0100
Message-ID: <200209091624.08766.dev@archonet.com> (raw)
In-Reply-To: <02090620595601.02695@localhost>
References: <02090620595601.02695@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, jlparkinson@bigpond.com, tgl@sss.pgh.pa.us
Subject: Re: Slow Multi-joins performance [DEVELOPERS attn please]
In-Reply-To: <200209091624.08766.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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