Diego de Lima wrote: > Hi list, > > I´m using Pg 8.0.3 on Linux FC2. > > This question may have a very simple answer (I hope), but I´m having > lots of trouble solving it, and I counldn´t find any other post about it > or anything in the pg docs. > > I have some very complex select statements on 4 million rows > tables. When using LEFT JOIN ON, some select statements takes about 2 > minutes. When I write exactly the same statement but with LEFT JOIN > USING, it takes only 1 minute. Comparing to Oracle, the same statement > takes 1 minute also, but with LEFT JOIN ON. > > Sometimes tables have the same column names and I can use LEFT JOIN > USING, but in some other cases I MUST use LEFT JOIN ON, because the > tables have different column names. > > So my question is: is there a way to make LEFT JOIN ON uses the same > plan of LEFT JOIN USING? > > Thanks, > > Diego de Lima > >
I'm guessing that ON/USING isn't the specific problem. It's probably more an issue of how the planner is deciding to do the joins (merge join, hash join, nested loop, etc.) Can you send the results of EXPLAIN ANALYZE <your query>? Also, any sort of join where you have to join against millions of rows is going to be slow. I don't know your specific design, but likely you could change the design to be more selective at an earlier level, which means that you can cut the size of the join by a lot. If you post you query, a lot of times people here can help optimize your query. (But make sure to explain what you are trying to do, so the optimizations make sense.) John =:->
signature.asc
Description: OpenPGP digital signature
