agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedfast way to run a query with 7 thousand constant values
3+ messages / 3 participants
[nested] [flat]
* fast way to run a query with 7 thousand constant values
@ 2025-03-01 18:23 Sbob <sbob@quadratum-braccas.com>
0 siblings, 2 replies; 3+ messages in thread
From: Sbob @ 2025-03-01 18:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: fast way to run a query with 7 thousand constant values
@ 2025-03-01 19:18 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2025-03-01 19:18 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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:
https://www.reddit.com/r/SQL/comments/inufa5/comment/g49vxrp/?utm_source=share&utm_medium=web3x&...
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: fast way to run a query with 7 thousand constant values
@ 2025-03-01 19:20 shammat@gmx.net
parent: Sbob <sbob@quadratum-braccas.com>
1 sibling, 0 replies; 3+ messages in thread
From: shammat@gmx.net @ 2025-03-01 19:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-01 19:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-01 18:23 fast way to run a query with 7 thousand constant values Sbob <sbob@quadratum-braccas.com>
2025-03-01 19:18 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-01 19:20 ` shammat@gmx.net
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox