Skip site navigation (1) Skip section navigation (2)

Re: IN vs =

From: "Lukas" <lukas(at)fmf(dot)vtu(dot)lt>
To: pgsql-novice(at)postgresql(dot)org
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: IN vs =
Date: 2009-01-27 10:10:34
Message-ID: 19249.217.117.29.29.1233051034.squirrel@fmf.vgtu.lt (view raw or flat)
Thread:
Lists: pgsql-novice
>>  I would like to ask, what is the main difference between operators IN
>> and
>> '='.
>>  Then I use operator IN in JOIN it gives me much worse time (in my
>> example
>> ~3000ms) at the same time '=' gives 30ms!
>>  But the most interesting think is that at the begging (when DB was
>> smaller) worked at the same speed as '=', why?
>
> Was it also on a different PG release back then?

 No, it was done on the same database (and same DBMS).

>> LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas,
>> ms_padengimas))
>
>>                                 Join Filter: (b_mokejimai.mok_id = ANY
>> (ARRAY[b_mokejimu_sudengimai.ms_mokejimas,
>> b_mokejimu_sudengimai.ms_padengimas]))
>
> The latest 8.2.x and 8.3.x releases contain a patch that avoids using
> this construct when there are variables on the right-hand side; I think
> that's your problem.

 Yes, we found the solution as shown, the question is why it is happening so?
 And also, what this patch does? changes the way how operator IN works?


> (FWIW, most people would probably say that having to write a join like
> this suggests you need to refactor your database structure...)

 Please comment it more - what is wrong with this join? (maybe you mean
that it has to many joins in one query? Also, what do you mean by
"refactor"?


--
Lukas
UAB nSoft
http://www.nsoft.lt
Lukas at nsoft.lt
+370 655 10 655




-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.


In response to

Responses

pgsql-novice by date

Next:From: LukasDate: 2009-01-27 10:27:35
Subject: Select START and MAXVALUE from Sequence
Previous:From: Robert SchnabelDate: 2009-01-27 00:21:23
Subject: Re: postgres.exe 100% CPU but no I/O

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group