Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5138C4760B7 for ; Fri, 17 Jan 2003 08:29:36 -0500 (EST) Received: from clearmetrix.com (unknown [209.92.142.67]) by postgresql.org (Postfix) with ESMTP id 9F009476078 for ; Fri, 17 Jan 2003 08:29:35 -0500 (EST) Received: from clearmetrix.com (chw.muvpn.clearmetrix.com [172.16.1.3]) by clearmetrix.com (8.11.6/linuxconf) with ESMTP id h0HDTNM10103; Fri, 17 Jan 2003 08:29:23 -0500 Message-ID: <3E280535.1090909@clearmetrix.com> Date: Fri, 17 Jan 2003 08:29:25 -0500 From: "Charles H. Woloszynski" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bruce Momjian Cc: Tom Lane , Roman Fail , josh@agliodbs.com, pgsql-performance@postgresql.org Subject: Re: 7.3.1 New install, large queries are slow References: <200301161618.h0GGIZi16755@candle.pha.pa.us> In-Reply-To: <200301161618.h0GGIZi16755@candle.pha.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/175 X-Sequence-Number: 822 I'd love to see this as a TODO item, but I am hardly one to add to the list... Charlie Bruce Momjian wrote: >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) >> >> >> > > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com