On Friday 06 Sep 2002 11:59 am, [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
