I have a table:

    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:

FROM blacklist b
     (SELECT id
      FROM blacklist 
      WHERE device_id = :deviceId
      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.

  • 1
    Unrelated, but: why numeric for the ID? integer or bigint would be more efficient. Sep 10, 2022 at 8:23
  • I actually copied from an old Oracle schema where the column is defined as NUMBER(20, 0) and converted into NUMERIC. Thanks. Sep 10, 2022 at 8:54
  • Sounds like this should really be two separate tables Sep 11, 2022 at 2:15

1 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
  • Would on a big table, if select null from blacklist where phone_number = :phone_number and select null from blacklist where device_id = :device_id execute, do they execute in parallel or synchronously? Sep 10, 2022 at 8:46
  • 1
    @HasanCanSaral Depends on the number of processors you have and workers configured in postgresql.conf - the server is capable of launching parallel workers - run EXPLAIN ANALYZE to check exactly what will happen.
    – Vérace
    Sep 10, 2022 at 13:48
  • 1
    No matter how big the table is, an existence check using an index is not going to take long enough to benefit from parallelism. We’re talking about 3 IOs per branch, probably mostly served by cache. It would be more effort for the RDBMS to setup additional threads and coordinate them, and all the branches of the union would need to be executed as no thread would know if another thread found rows without waiting for it to complete. Sep 10, 2022 at 16:39
  • I see, so there’s no way this query return without completing both sides of UNION ALL. Because in application layer, if it can, I can measure it does benefit from parallelism, particularly in worst cases/lookups. Sep 11, 2022 at 5:45
  • @HasanCanSaral I don’t follow your comment, but to be 100% clear: The code I wrote and demoed in the DBFiddle uses serial execution and stops as soon as it finds a match - and because it’s just doing basic equality checks against indexed columns that is going to be pretty instant, you can see this in the row source statistics that are included. The statistics will be pretty similar no matter how large the table is. You do not need parallelism for performance here as long as you’re just checking existence via equality filters on indexed columns Sep 11, 2022 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.