LEFT JOIN ON vs. LEFT JOIN USING performance

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

"Diego de Lima" <diego(at)sistemica(dot)info> writes:
> 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.

Could we see details please? Like the table schemas, the query itself,
and EXPLAIN ANALYZE results for both cases.

regards, tom lane

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
=:->