Simple IN vs IN values performace

From: Oleksandr Voytsekhovskyy <av(at)uniweb(dot)ua>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Simple IN vs IN values performace
Date: 2021-02-22 15:00:43
Message-ID: F39A0ECE-1FB0-41BA-9429-3C617C51E6A6@uniweb.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message João Gaspar 2021-02-22 15:03:30 Re: Problem enabling LDAP login
Previous Message Luca Ferrari 2021-02-22 14:42:38 cannot promote after recovery for PITR