Re: Slow Multi-joins performance [DEVELOPERS attn please]

On Friday 06 Sep 2002 11:59 am, jlparkinson(at)bigpond(dot)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