Postgres Pro
Downloads
Home   >   mailing lists

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

fast way to run a query with 7 thousand constant values

From
Sbob
Date:
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?

Thanks in advance

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

From
Ron Johnson
Date:
On Sat, Mar 1, 2025 at 1:23 PM Sbob <sbob@quadratum-braccas.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:

Or EXISTS:

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!

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

From
shammat@gmx.net
Date:
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.