Re: fast way to run a query with 7 thousand constant values
| От | Ron Johnson |
|---|---|
| Тема | Re: fast way to run a query with 7 thousand constant values |
| Дата | |
| Msg-id | CANzqJaDaG3RB-mFevAOF5y1ezMJ054bpDAA-B=DZd0=PagpxPQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | fast way to run a query with 7 thousand constant values (Sbob <sbob@quadratum-braccas.com>) |
| Список | pgsql-admin |
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!
В списке pgsql-admin по дате отправления: