Re: 7.3.1 New install, large queries are slow
От | Charles H. Woloszynski |
---|---|
Тема | Re: 7.3.1 New install, large queries are slow |
Дата | |
Msg-id | 3E280535.1090909@clearmetrix.com обсуждение исходный текст |
Ответ на | Re: 7.3.1 New install, large queries are slow (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | pgsql-performance |
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" <rfail@posportal.com> 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
В списке pgsql-performance по дате отправления: