>>> On Wed, Feb 1, 2006 at 1:34 pm, in message <3759.1138822464@xxxxxxxxxxxxx>, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes: >> We do have a few queries where PostgreSQL is several orders of >> magnitude slower. It appears that the reason it is choosing a bad plan >> is that it is reluctant to start from a subquery when there is an outer >> join in the FROM clause. > > AFAICT this case doesn't really hinge on the outer join at all. The > problem is that EXISTS subqueries aren't well optimized. I would have > expected an equivalent IN clause to work better. In fact, I'm not > clear why the planner isn't finding the cheapest plan (which it does > estimate as cheapest) from the IN version you posted. All I know is that trying various permutations, I saw it pick a good plan for the IN format when I eliminated the last outer join in the FROM clause. I know it isn't conclusive, but it was a correlation which suggested a possible causality to me. The EXISTS never chose a reasonable plan on this one, although we haven't had a problem with them in most cases. > What PG version is this exactly? select version() reports: PostgreSQL 8.1.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) However, this was actually built off the 8.1 stable branch as of Jan. 13th at about 3 p.m. This build does contain the implementation of standard_conforming_strings for which I recently posted a patch. The make was configured with: --enable-integer-datetimes --enable-debug --disable-nls > >> ... The third query is the fastest, but isn't >> portable enough for our mixed environment. > > Not really relevant to the problem, but what's wrong with it? Looks > like standard SQL to me. It is absolutely compliant with the standards. Unfortunately, we are under a "lowest common denominator" portability mandate. I notice that support for this syntax has improved since we last set our limits; I'll try to get this added to our allowed techniques. I can't complain about the portability mandate -- without it, we would undoubtedly have had product specific code for the commercial product which would have made migration to PostgreSQL much more painful. -Kevin
