If I read this right, intarray is for reading values from an array
data type.
I don't have this.
I have a varchar() field that is indexed (unique), call it 'foo'
I have a choice of running:
SELECT bar FROM tokens WHERE foo IN ('apple','orange','biscuit'....)
for up to ~300 words
OR
SELECT bar FROM tokens WHERE foo = 'apple' up to ~300 times as a
prepared/cached SQL statements.
On Apr 6, 2007, at 9:10 AM, Oleg Bartunov wrote:
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@xxxxxxxxxxxxxx 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@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
