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

Re: IN vs =

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lukas" <lukas(at)fmf(dot)vtu(dot)lt>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: IN vs =
Date: 2009-01-26 21:33:25
Message-ID: 29955.1233005605@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Lukas" <lukas(at)fmf(dot)vtu(dot)lt> writes:
>  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?

> 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.

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

			regards, tom lane

In response to

  • IN vs = at 2009-01-26 20:48:08 from Lukas

Responses

pgsql-novice by date

Next:From: Robert SchnabelDate: 2009-01-27 00:21:23
Subject: Re: postgres.exe 100% CPU but no I/O
Previous:From: LukasDate: 2009-01-26 20:48:08
Subject: IN vs =

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