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
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.
numericfor the ID?
bigintwould be more efficient.
NUMBER(20, 0)and converted into