Re: "IN" statement causing execution cancel?

On Wednesday 21 May 2003 10:16 pm, Andrew Ayers wrote:
> All,
>
> I am having (yet another) problem.
>
> I am attempting to execute, via ODBC (thus, the PostgreSQL ODBC driver
> under Windows), a statement of the form:
>
> SELECT field1 FROM table WHERE field2 IN (list);
>
> Where "list" is a *very* long comma-delimited list of values (on the
> order of several hundred values).

Good to see you've solved it by extending a timeout, but you might find it
executes much faster if you:
1. CREATE TEMP TABLE search_list
2. Insert thousands of values
3. Join against search_list

Especially if your thousands don't change much, then you could make the
search_list table permanent.

Do a search on the mailing list archives for "IN","slow","EXISTS" - PG's
current handling of IN isn't as smart as it might be.

--
Richard Huxton