how to make duplicate finding query faster?

On 30 Dec 2020, at 19:59, Sachin Kumar wrote:

This will not suffice our requirement. we have to validate that there
would not be any duplicate value in DB. we have done that earlier by
leaving DB to check if there is any duplicate and found duplicate value in
DB.

If the constraint was in place before any data was inserted into the database then there will be no duplicates. Adding a constraint to a database will not remove existing data which violates the constraint.

we have a table "card" and a single column "number " which we are updated
with a csv file with 600k numbers and require no duplicate number should be
there in the table.

One trap is that the data may not be the same even if it’s a “duplicate”. The “number” may have spaces or other human readable characters. It is a duplicate from the human perspective but not the machine. These “duplicates” will be also be very hard to find with a simple query. If OP is not already doing this I suggest the “number” should be stored in the database as BIGINT eg.

cc_number BIGINT NON NULL UNIQUE,  — this will prevent any addition of a duplicate cc_number
PRIMARY KEY (cc_number)		— or, more simply, in one line: cc_number BIGINT PRIMARY KEY

Obviously the csv will have to be checked to ensure the number is a legal integer value.

There is nothing in the original post to say what is contained in the card field. And it has not been stated if this field is also unique. Assuming it has text then this will be more interesting to work with as a human may see the duplication but not the machine, but if it’s only the number that has to be unique then it is straight forward.

Please if can have a faster query can help us in achieving this requirement.

As noted by Holgar Jacobs up thread it is not possible for a query to be faster than checks done at time the data is inserted. The data has to be inserted and indexes created etc. If the duplicate key constraint is violated then the duplicate is found. Otherwise the data has to be inserted and a query run afterwards.

Assuming there is a reasonably recent PostgreSQL hidden behind the Django and Python, i.e., >9.5, then the INSERT can be attempted with

INSERT INTO  …  ON CONFLICT DO NOTHING — https://www.postgresql.org/about/news/postgresql-131-125-1110-1015-9620-and-9524-released-2111/

Once this is in place the data will be added to the table as fast as that is possible, and with no duplicates. It will be very much slower if every insertion has to be checked in the Django+Python stack. Basically if speed is needed go to the database and talk in SQL.

Regards
Gavan Schneider (who can offer no help with Dango/Python)