From: | assaf lehr <assaf_lehr(at)yahoo(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5165: Poor performance with Left-join where right side does not exist |
Date: | 2009-11-05 14:21:24 |
Message-ID: | 255237.92372.qm@web53701.mail.re2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I did not not check in 8.4 and maybe it is solved there, but let me explain it better.
My particular case is a Join of two tables where the other table result should be "is null".
Using explain, I found out that nested-loop and hash-join merge algrothims are bad for me [both tables are quite big].
The merge-join algorithm looked like a great way to solve the problem , as both the table has the same ordered index , which is just the thing the merge-join needs.
The two tables and the slow join are:
A
category | idB | multiple-values
B
category | idB | multiple-values
--------------
select B.idB , A.idB from B left join A on B.idB = A.idB and
A.category=B.category
where A.idB is null [and A.category=202] limit 10
--------------
Mind you that if the last where is switched from "A.idB is null" to any regular check which can be done on the one row (like colC=5) , the merge join works just fine (and fast).
In the "is null" case however, the performance is very poor. I would guess that there is a special case in the postgres source-code for the "is null" which has a different behavior. It can be, because unlike a single-row condition, is-null needs a different behaviour.
assaf
________________________________
From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: assaf <assaf_lehr(at)yahoo(dot)com>; pgsql-bugs(at)postgresql(dot)org
Sent: Thu, November 5, 2009 3:07:41 AM
Subject: Re: [BUGS] BUG #5165: Poor performance with Left-join where right side does not exist
On Wed, Nov 4, 2009 at 3:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "assaf" <assaf_lehr(at)yahoo(dot)com> writes:
>> PostgreSQL version: 8.37
>> Description: Poor performance with Left-join where right side does
>> not exist
>
> 8.4 might be smarter about this case for you. It's hard to tell for sure
> with so few details.
EXPLAIN output would be a good place to start, and EXPLAIN ANALYZE for
the queries that run quickly enough that you can let them run to
completion.
...Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-11-05 15:10:07 | Re: BUG #5166: readdir() failure on Mac OS-X is HFS "feature" |
Previous Message | Alvaro Herrera | 2009-11-05 13:32:02 | Re: BUG #5166: readdir() failure on Mac OS-X is HFS "feature" |