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

On Sat, Mar 1, 2025 at 1:23 PM Sbob <sbob(at)quadratum-braccas(dot)com> wrote:

> All;
>
> 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?
>

200ms in the planning stage? I'd sell my first grandchild to get the
complex queries I see down from 10000 ms.

Anyway... you can use VALUES in a CTE to generate an anonymous table:
https://www.reddit.com/r/SQL/comments/inufa5/comment/g49vxrp/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

Or EXISTS:
https://dba.stackexchange.com/a/33048/63913

Because they're constants, I'd probably try the CTE + VALUES method.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!