Re: Simple IN vs IN values performace

Hi

út 23. 2. 2021 v 10:36 odesílatel Alexander Voytsekhovskyy <
young(dot)inbox(at)gmail(dot)com> napsal:

> Thanks for suggestion
>
> with tmp tables there are another issue - there are already 5-6 tables and
> 2-3 IN filters. If i will replace them with tmp tables it may hit query
> planner limits and it will become to produce terrible query plans, for
> example when genetic query optimizer starts
>

you can increase these limits - they are relatively low, and can be
increased on modern CPU.

https://www.postgresql.org/docs/current/runtime-config-query.html

Regards

Pavel

> On Tue, Feb 23, 2021 at 1:45 AM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On 2/22/21 9:00 AM, Oleksandr Voytsekhovskyy wrote:
>>
>> Greetings,
>>
>> We have queries with IN filters with long list of INT values
>>
>> Sometimes, they running extremely slow, and I have found suggestion to
>> use syntax
>>
>> Field IN (VALUES(1465), (1478), ...
>>
>> Instead of
>>
>> Field IN (1465, 1478, ...
>>
>> On some cases it helps, but on other it makes query running 1000+ times
>> slower
>>
>>
>> Insert the values into a TEMPORARY TABLE, then join that to your main
>> table?
>>
>>
>> So the issue is:
>> - some queries much faster with simple IN
>> - some queries much faster with IN + VALUES
>>
>> Here is explain on case when it’s extremely slow:
>>
>> -> HashAggregate (cost=5.78..9.62 rows=385 width=4)
>> Group Key: ""*VALUES*"".column1"
>> -> Values Scan on ""*VALUES*"" (cost=0.00..4.81 rows=385 width=4)"
>>
>> What is the right way to pass long INT values list to IN filter?
>>
>> I am using PostgreSQL 13.1 on Ubuntu
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>