pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLEFT JOIN ON vs. LEFT JOIN USING performance
3+ messages / 3 participants
[nested] [flat]
* LEFT JOIN ON vs. LEFT JOIN USING performance
@ 2005-08-19 15:22 Diego de Lima <diego@sistemica.info>
2005-08-19 15:40 ` Re: LEFT JOIN ON vs. LEFT JOIN USING performance Tom Lane <tgl@sss.pgh.pa.us>
2005-08-19 15:40 ` Re: LEFT JOIN ON vs. LEFT JOIN USING performance John A Meinel <john@arbash-meinel.com>
0 siblings, 2 replies; 3+ messages in thread
From: Diego de Lima @ 2005-08-19 15:22 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LEFT JOIN ON vs. LEFT JOIN USING performance
2005-08-19 15:22 LEFT JOIN ON vs. LEFT JOIN USING performance Diego de Lima <diego@sistemica.info>
@ 2005-08-19 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-08-19 15:40 UTC (permalink / raw)
To: Diego de Lima <diego@sistemica.info>; +Cc: pgsql-performance
"Diego de Lima" <diego@sistemica.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: LEFT JOIN ON vs. LEFT JOIN USING performance
2005-08-19 15:22 LEFT JOIN ON vs. LEFT JOIN USING performance Diego de Lima <diego@sistemica.info>
@ 2005-08-19 15:40 ` John A Meinel <john@arbash-meinel.com>
1 sibling, 0 replies; 3+ messages in thread
From: John A Meinel @ 2005-08-19 15:40 UTC (permalink / raw)
To: Diego de Lima <diego@sistemica.info>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-19 15:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-19 15:22 LEFT JOIN ON vs. LEFT JOIN USING performance Diego de Lima <diego@sistemica.info>
2005-08-19 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-19 15:40 ` John A Meinel <john@arbash-meinel.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox