Parallel and faster evaluation of SELECTing boolean comparing count

Question

I have a table:

CREATE TABLE IF NOT EXISTS blacklist
(
    id                    NUMERIC(20, 0)                           NOT NULL DEFAULT NEXTVAL('blacklist_sequence') PRIMARY KEY,
    device_id             VARCHAR(512)   DEFAULT NULL              NULL,
    phone_number          VARCHAR(512)   DEFAULT NULL              NULL
);

CREATE INDEX index_phone ON blacklist (phone_number);
CREATE INDEX index_device ON blacklist (device_id);

and I want to use this blacklist to see whether a customer with device_id or a phone_number is inside of it. So my query is:

SELECT CASE WHEN COUNT(*) > 0 THEN TRUE ELSE FALSE END
FROM blacklist b
         JOIN
     (SELECT id
      FROM blacklist 
      WHERE device_id = :deviceId
      UNION
      SELECT id
      FROM blacklist 
      WHERE phoneNumber = :phoneNumber) filtered_table ON b.id = filtered_table.id

Basically splitting the OR condition into sub queries with UNION to make use of indexes, but I've discovered that we have so many blacklist rows with phone_number row filled whereas fewer with device_id.

So, I'm looking for a way to parallelize even further so that when a device_id match is found, I don't have to keep looking to collect and UNION the results of phone_number condition (because at that point I already know that my SELECT will return TRUE) to come up with a greater COUNT, since I'm only interested if the row exists or not.

How do I rewrite this query to achieve it? Though I'm mainly looking for a Postgres solution, I'd be interested in finding out what other DBs can do.

Answer 1

You’ve written the query so that it has to find every matching row, then lookup the row again to count it. You just want an existence test using an exists predicate, this will return as soon as a match is found.

See this dbfiddle for a demo https://dbfiddle.uk/Awu5NUrI you can see that the second branch of the union all is not executed because it successfully found a match using a quick index check in the first branch.

You don’t need parallelism, you just need to do less work.

select 1
where exists 
  (select null
  from blacklist where phone_number = :phone_number
  union all
  select null
  from blacklist where device_id = :device_id
  )