From: "Gavan Schneider" <list.pg.gavan@pendari.org>
To: "Sachin Kumar" <sachinkumaras@gmail.com>
Cc: "Holger Jakobs" <holger@jakobs.com>, krishna@thewebconz.com,
 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>
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>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="=_MailMate_0FDDB1C0-6436-4FC9-BF01-9DFB01A8A54E_="
Precedence: bulk


--=_MailMate_0FDDB1C0-6436-4FC9-BF01-9DFB01A8A54E_=
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: quoted-printable

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=E2=80=99s a =

=E2=80=9Cduplicate=E2=80=9D. The =E2=80=9Cnumber=E2=80=9D may have  space=
s or other human =

readable characters. It is a duplicate from the human perspective but =

not the machine. These =E2=80=9Cduplicates=E2=80=9D will be also be very =
hard to =

find with a simple query. If OP is not already doing this I suggest the =

=E2=80=9Cnumber=E2=80=9D should be stored in the database as BIGINT eg.

	cc_number BIGINT NON NULL UNIQUE,  =E2=80=94 this will prevent any addit=
ion =

of a duplicate cc_number
	PRIMARY KEY (cc_number)		=E2=80=94 or, more simply, in one line: cc_numb=
er =

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=E2=80=99s =
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  =E2=80=A6  ON CONFLICT DO NOTHING =E2=80=94 =

https://www.postgresql.org/about/news/postgresql-131-125-1110-1015-9620-a=
nd-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)

--=_MailMate_0FDDB1C0-6436-4FC9-BF01-9DFB01A8A54E_=
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html>
<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/xhtml; charset=3Dutf-8"=
>
</head>
<body>
<div style=3D"font-family:sans-serif"><div style=3D"white-space:normal">
<p dir=3D"auto">On 30 Dec 2020, at 19:59, Sachin Kumar wrote:</p>
</div><div style=3D"white-space:normal"><blockquote style=3D"border-left:=
2px solid #777; color:#777; margin:0 0 5px; padding-left:5px"><p dir=3D"a=
uto">This will not suffice our requirement. we have to validate that ther=
e
<br>
would not be any duplicate value in DB. we have done that earlier by
<br>
leaving DB to check if there is any duplicate and found duplicate value i=
n
<br>
DB.
</p>
</blockquote></div>
<div style=3D"white-space:normal">
<p dir=3D"auto">If the constraint was in place <em>before</em>  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 th=
e constraint.</p>
</div><div style=3D"white-space:normal"><blockquote style=3D"border-left:=
2px solid #777; color:#777; margin:0 0 5px; padding-left:5px"><p dir=3D"a=
uto">we have a table "card"  and a single column "number " which we are u=
pdated
<br>
with a csv file with 600k numbers and require no duplicate number should =
be
<br>
there in the table.
</p>
</blockquote></div>
<div style=3D"white-space:normal">
<p dir=3D"auto">One trap is that the data may not be the same even if it=E2=
=80=99s a =E2=80=9Cduplicate=E2=80=9D. The =E2=80=9Cnumber=E2=80=9D may h=
ave  spaces or other human readable characters. It is a duplicate from th=
e human perspective but not the machine. These =E2=80=9Cduplicates=E2=80=9D=
 will be also be very hard to find with a simple query. If OP is not alre=
ady doing this I suggest the =E2=80=9Cnumber=E2=80=9D should be stored in=
 the database as BIGINT eg.</p>
<pre style=3D"background-color:#F7F7F7; border-radius:5px 5px 5px 5px; ma=
rgin-left:15px; margin-right:15px; max-width:90vw; overflow-x:auto; paddi=
ng:5px" bgcolor=3D"#F7F7F7"><code style=3D"background-color:#F7F7F7; bord=
er-radius:3px; margin:0; padding:0" bgcolor=3D"#F7F7F7">cc_number BIGINT =
NON NULL UNIQUE,  =E2=80=94 this will prevent any addition of a duplicate=
 cc_number
PRIMARY KEY (cc_number)		=E2=80=94 or, more simply, in one line: cc_numbe=
r BIGINT PRIMARY KEY
</code></pre>
<p dir=3D"auto">Obviously the csv will have to be checked to ensure the n=
umber is a legal integer value.</p>
<p dir=3D"auto">There is nothing in the original post to say what is cont=
ained 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=E2=
=80=99s only the number that has to be unique then it is straight forward=
=2E</p>
</div><div style=3D"white-space:normal"><blockquote style=3D"border-left:=
2px solid #777; color:#777; margin:0 0 5px; padding-left:5px"><p dir=3D"a=
uto">Please if can have a faster query can help us in achieving this requ=
irement.
</p>
</blockquote></div>
<div style=3D"white-space:normal">
<p dir=3D"auto">As noted by Holgar Jacobs up thread  it is not possible f=
or a query to be faster than checks done at time the data is inserted. Th=
e data has to be inserted and indexes created etc. If the duplicate key c=
onstraint is violated then the duplicate is found. Otherwise the data has=
 to be inserted and a query run afterwards.</p>
<p dir=3D"auto">Assuming there is a reasonably recent PostgreSQL hidden b=
ehind the Django and Python, i.e., &gt;9.5, then the INSERT can be attemp=
ted with</p>
<pre style=3D"background-color:#F7F7F7; border-radius:5px 5px 5px 5px; ma=
rgin-left:15px; margin-right:15px; max-width:90vw; overflow-x:auto; paddi=
ng:5px" bgcolor=3D"#F7F7F7"><code style=3D"background-color:#F7F7F7; bord=
er-radius:3px; margin:0; padding:0" bgcolor=3D"#F7F7F7">INSERT INTO  =E2=80=
=A6  ON CONFLICT DO NOTHING =E2=80=94 https://www.postgresql.org/about/ne=
ws/postgresql-131-125-1110-1015-9620-and-9524-released-2111/
</code></pre>
<p dir=3D"auto">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 muc=
h 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.</p>
<p dir=3D"auto">Regards<br>
Gavan Schneider (who can offer no help with Dango/Python)</p>

</div></div>
</body>
</html>

--=_MailMate_0FDDB1C0-6436-4FC9-BF01-9DFB01A8A54E_=--