Postgres query optimizer in empty sets

Question

Imagine I have the following query (translated from JDBC):

select ta.indexedColumnA, tb.indexedColumnB
from tableA ta join tableB tb on ta.id=tb.foreignKeyA
where ta.id in (@listOfIds)

Now, the param @listOfIds can be a list with some ids (let's say [0,10] usually).

In case it's an empty list, is the query optimiser able to do some optimisation (such as return an empty result) ?
does the capability of optimising (short-circuit) depends on something we can control?

(I'm not sure of postgres version, will update as soon as I can be sure)

You can't really pass an empty list because in () is invalid SQL — user330315
– user330315, Commented Jan 17, 2020 at 11:13
@pedrorijo would be an option to pass null instead of nothing as parameter? where ta.id in (null) — Jim Jones
– Jim Jones, Commented Jan 17, 2020 at 12:05
@JimJones: that's actually a good idea ;) Postgres will catch that and don't read anything from the table at all. dbfiddle.uk/… — user330315
– user330315, Commented Jan 17, 2020 at 12:25
@a_horse_with_no_name I also think that it might be the most elegant solution, I'm just not sure that it is what the OP needs :-D — Jim Jones
– Jim Jones, Commented Jan 17, 2020 at 12:29

Jim Jones · Accepted Answer · 2020-01-17 12:39:36Z

1

As mentioned by @a_horse_with_no_name: you cannot pass an empty set using IN. Take a look at the fiddle he provided in the comments.

What you can do is to pass a NULL value instead, which will return an empty result set:

Data sample

CREATE TEMPORARY TABLE t (id int, val int);
INSERT INTO t VALUES (1,1),(NULL,2);

Query

SELECT * FROM t 
WHERE id IN (NULL);

 id | val 
----+-----
(0 Zeilen)

answered Jan 17, 2020 at 12:39

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1