From: Gavan Schneider <list.pg.gavan@pendari.org>
To: Sachin Kumar <sachinkumaras@gmail.com>
Cc: Holger Jakobs <holger@jakobs.com>
Cc: krishna@thewebconz.com
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: how to make duplicate finding query faster?
Date: Wed, 30 Dec 2020 22:21:17 +1100
Message-ID: <43C74F51-3099-4787-8C73-CE928CEAB01A@pendari.org> (raw)
In-Reply-To: <CALg-PKAwst1C2fF9ZBY+Dog0D2CZ7P-xppG41b3DnV62R=SxwQ@mail.gmail.com>
References: <CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=mPtA@mail.gmail.com>
<5bf642e4-eb22-8eba-e96d-5b33b5010b03@jakobs.com>
<CALg-PKAwst1C2fF9ZBY+Dog0D2CZ7P-xppG41b3DnV62R=SxwQ@mail.gmail.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: list.pg.gavan@pendari.org, sachinkumaras@gmail.com, holger@jakobs.com, krishna@thewebconz.com, pgsql-admin@lists.postgresql.org
Subject: Re: how to make duplicate finding query faster?
In-Reply-To: <43C74F51-3099-4787-8C73-CE928CEAB01A@pendari.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox