From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Thomas Kellerer <shammat(at)gmx(dot)net> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How bad is using queries with thousands of values for operators IN or ANY? |
Date: | 2020-08-31 14:04:43 |
Message-ID: | CAFj8pRDP3sugL2CWM0FEm=qa4=RynEZy3kHBpUyRzrQWfHtWbw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
po 31. 8. 2020 v 13:29 odesílatel Thomas Kellerer <shammat(at)gmx(dot)net> napsal:
> Thorsten Schöning schrieb am 31.08.2020 um 12:37:
> > So for what query size or number of IDs to compare in IN would you
> > consider a different approach at all?
>
>
> In my experience "hundreds" of IDs tend to be quite slow if used with an
> IN clause.
>
> Rewriting the IN to a JOIN against a VALUES clause is very often faster:
>
> So instead of:
>
> select *
> from t
> where id in (1,2,3, .... ,500);
>
> using this:
>
> select *
> from t
> join (
> values (1),(2),(3),...(500)
> ) as x(id) on x.id = t.id
>
> produces more often than not a more efficient execution plan (assuming no
> values are duplicated in the IN list)
>
> Obviously I don't know if such a re-write is even feasible though.
>
yes - this query probably will have a slow start, but the execution will be
fast. Unfortunately, there are not available statistics.
> Thomas
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Godfrin, Philippe E | 2020-08-31 16:38:05 | Numeric data types |
Previous Message | Thomas Boussekey | 2020-08-31 14:01:34 | Re: When are largobject records TOASTed into pg_toast_2613? |