Re: SQL WHERE: many sql or large IN()

Tom,

have you seen contrib/intarray ?

Oleg
On Fri, 6 Apr 2007, tom wrote:

> I'm wondering where the differences are in running two different types of SQL
> statements.
>
> Given ~300 tokens/words I can either run 1 sql statement with a large list in
> a "WHERE foo IN (...300 tokens...)"
> or I can run ~300 statements, one for each token.
> In the first case, the SQL is not prepared, but just executed.
> In the second case, the SQL is prepared and run as a cached execution plan (I
> think).
>
> Now.  It would seem that the second approach would be painfully slow.  But
> I'm not sure that I'm seeing this.
> Currently I have <5 users.  As always, this might change...
>
> Before I start going about coding and testing lots of stuff I thought I would
> ask for some historical experiences someone might have had when comparing
> these two approaches and if there are inflection points between the
> performance in terms of the number of tokens or simultaneous users.
>
> I should add that the tokens are either indexed or primary indexed but in
> both cases, unique, and not guaranteed to exist in every case.
>
> Initially it seems that the WHERE IN (...) approach takes a turn for the
> worse when the list gets very large.
> It also seems to do comparatively worse when the number of tokens is very
> small.
> But I can't claim any scientifically sound basis for making this distinction.
>
> Any experiences someone would like to share?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>     subscribe-nomail command to majordomo@postgresql.org so that your
>     message can get through to the mailing list cleanly

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83