Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 32AB14766BA for ; Thu, 16 Jan 2003 11:19:48 -0500 (EST) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id 1E463476A4A for ; Thu, 16 Jan 2003 11:18:52 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id h0GGIZi16755; Thu, 16 Jan 2003 11:18:35 -0500 (EST) From: Bruce Momjian Message-Id: <200301161618.h0GGIZi16755@candle.pha.pa.us> Subject: Re: 7.3.1 New install, large queries are slow In-Reply-To: <3E26D564.1060209@clearmetrix.com> To: "Charles H. Woloszynski" Date: Thu, 16 Jan 2003 11:18:35 -0500 (EST) Cc: Tom Lane , Roman Fail , josh@agliodbs.com, pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/154 X-Sequence-Number: 801 Is this a TODO item? --------------------------------------------------------------------------- Charles H. Woloszynski wrote: > I was surprised to hear that JOIN syntax constrained the planner. We > have a policy of using JOIN syntax to describe the table relationships > and where clauses to describe the selection process for our queries. It > was our understanding that the JOIN syntax was introduced to support > this approach, but not to contrain the planner. > > Is there any way to sell the planner to consider JOIN syntax as > equivalent to WHERE clauses and to not use them to force the planner > down a specific path? Can we get that added as an option (and then made > available to use JDBC folks as a URL parameter). It would make my team > very happy :-). > > > I think that making this an option will help all those migrating to > Postgres who did not expect that JOINs forced the planner down specific > plans. Is it possible/reasonable to add? > > Charlie > > > Tom Lane wrote: > > >"Roman Fail" writes: > > > > > >>Thanks to everyone for the quick replies! I'm sure that my lack of > >>skill with SQL queries is the main problem. What's strange to me is > >>how MSSQL takes my bad queries and makes them look good anyway. It > >>must have a real smart planner. > >> > >> > > > >I think more likely the issue is that your use of JOIN syntax is forcing > >Postgres into a bad plan. MSSQL probably doesn't assign any semantic > >significance to the use of "a JOIN b" syntax as opposed to "FROM a, b" > >syntax. Postgres does. Whether this is a bug or a feature depends on > >your point of view --- but there are folks out there who find it to be > >a life-saver. You can find some explanations at > >http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html > > > > > > > >>Is it pretty much universally accepted that I should drop all my > >>foreign keys? > >> > >> > > > >No. They don't have any effect on SELECT performance in Postgres. > >They will impact update speed, but that's not your complaint (at the > >moment). Don't throw away data integrity protection until you know > >you need to. > > > > regards, tom lane > > > >---------------------------(end of broadcast)--------------------------- > >TIP 4: Don't 'kill -9' the postmaster > > > > > > -- > > > Charles H. Woloszynski > > ClearMetrix, Inc. > 115 Research Drive > Bethlehem, PA 18015 > > tel: 610-419-2210 x400 > fax: 240-371-3256 > web: www.clearmetrix.com > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073