Tom Lane schrieb:
Richard Huxton <dev@archonet.com> writes:
[EMAIL PROTECTED] wrote:
I am using PostgreSQL (7.4) with a schema that was generated
automatically (using hibernate). The schema consists of about 650
relations. One particular query (also generated automatically)
consists of left joining approximately 350 tables.
May I be the first to offer an "ouch"!
Seconded.
However, I'm not sure how much leeway there is in
planning a largely left-joined query.
Not much. The best hope for a better result is to order the LEFT JOIN
clauses in a way that will produce a good plan.
If this is the best way, you should consider to use an sql query and not
the hibernate ql language in this case. This is possible with Hibernate!
I suppose you could also consider a view in Postgre and let Hibernate
read from this view. This is also possible.
One thought is that I am not sure I believe the conclusion that planning
is taking only 36 ms; even realizing that the exclusive use of left
joins eliminates options for join order, there are still quite a lot of
plans to consider. You should try both EXPLAIN and EXPLAIN ANALYZE
from psql and see how long each takes. It'd also be interesting to keep
an eye on how large the backend process grows while doing this --- maybe
it's being driven into swap.
Also: I'm not sure there *is* such a thing as a good plan for a 350-way
join. It may be time to reconsider your data representation. If
Hibernate really forces this on you, it may be time to reconsider your
choice of tool.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Kind Regards / Viele Grüße
Sebastian Hennebrueder
-----
http://www.laliluna.de/tutorials.html
Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
