Re: fast way to run a query with 7 thousand constant values

Am 01.03.25 um 19:23 schrieb Sbob:
> I have a client that wants to pass in as an IN clause a list of
> 7,000 values. The value set changes for each query and it ranges
> from 5,000 to 8,000 values.
>
> The planning time is too long for the requirements. (250 - 300ms)
>
> I got it to work in 50ms end to end by creating a temp table and
> doing a copy from STDIN into the temp table
>
> However this is a Java based app and getting it to do a copy is
> becoming way more complex than it should be.
>
> Anyone know of an alternate way to run a query where an id is one of
> X values where X is a list of 5 - 8 thousand values that will not
> force the planner to spend 200+ms prepping the plan?

Use "where the_column = any(?)" and pass all the values as a single parameter
of type java.sql.Array using a PreparedStatement.