Subject: Re: how to make duplicate finding query faster?
To: pgsql-admin@lists.postgresql.org
References: 
 <CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=mPtA@mail.gmail.com>
From: Holger Jakobs <holger@jakobs.com>
Message-ID: <5bf642e4-eb22-8eba-e96d-5b33b5010b03@jakobs.com>
Date: Wed, 30 Dec 2020 09:23:42 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=mPtA@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="tg8qX3C02YF5jBgNy8TcZjhxwzB8Ad0F5"
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--tg8qX3C02YF5jBgNy8TcZjhxwzB8Ad0F5
Content-Type: multipart/mixed; boundary="xVmXS8SHkflbovkSkfLTptQAa9s8rItEa";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <5bf642e4-eb22-8eba-e96d-5b33b5010b03@jakobs.com>
Subject: Re: how to make duplicate finding query faster?
References: <CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=mPtA@mail.gmail.com>
In-Reply-To: <CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=mPtA@mail.gmail.com>

--xVmXS8SHkflbovkSkfLTptQAa9s8rItEa
Content-Type: multipart/alternative;
 boundary="------------60DB514A57D39D69C484C220"
Content-Language: de-DE

This is a multi-part message in MIME format.
--------------60DB514A57D39D69C484C220
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Am 30.12.20 um 08:36 schrieb Sachin Kumar:
> Hi All,
>
> I am uploading data into PostgreSQL using the CSV file and checking if =

> there is any duplicates value in DB it should return a duplicate=20
> error.=C2=A0 I am using below mention query.
>
> if Card_Bank.objects.filter( Q(ACCOUNT_NUMBER=3Dcard_number) ).exists()=
:
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 flag=3D2
> =C2=A0 =C2=A0 =C2=A0 else:
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 flag=3D1
> it is taking too much time i am using 600k cards in CSV.
>
> Kindly help me in making the query=C2=A0faster.
>
> I am using Python, Django & PostgreSQL.
> --=20
>
> Best Regards,
> Sachin Kumar

I think it would be easier to not check the duplicates before, but let=20
the DB complain about duplicates.

That would about slash the roundtrips to the DB in half. Instead of=20
check + insert there would be only an insert, which might fail every now =

and then.

Regards,

Holger

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


--------------60DB514A57D39D69C484C220
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">Am 30.12.20 um 08:36 schrieb Sachin
      Kumar:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=3DmPtA@mail.gm=
ail.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">Hi All,
        <div><br>
        </div>
        <div>I am uploading data into PostgreSQL using the CSV file and
          checking if there is any duplicates value in DB it should
          return a duplicate error.=C2=A0 I am using below mention query.=
</div>
        <div><br>
        </div>
        <div>if Card_Bank.objects.filter( Q(ACCOUNT_NUMBER=3Dcard_number)=

          ).exists(): <br>
          =C2=A0 =C2=A0 =C2=A0 =C2=A0 flag=3D2<br>
          =C2=A0 =C2=A0 =C2=A0 else:<br>
          =C2=A0 =C2=A0 =C2=A0 =C2=A0 flag=3D1<br clear=3D"all">
          <div>it is taking too much time i am using 600k cards in CSV.</=
div>
          <div><br>
          </div>
          <div>Kindly help me in making the query=C2=A0faster.</div>
          <div><br>
          </div>
          <div>I am using Python, Django &amp; PostgreSQL.</div>
          -- <br>
          <div dir=3D"ltr" class=3D"gmail_signature"
            data-smartmail=3D"gmail_signature"><br>
            Best Regards, <br>
            Sachin Kumar<br>
          </div>
        </div>
      </div>
    </blockquote>
    <p>I think it would be easier to not check the duplicates before,
      but let the DB complain about duplicates.</p>
    <p>That would about slash the roundtrips to the DB in half. Instead
      of check + insert there would be only an insert, which might fail
      every now and then.</p>
    <p>Regards,</p>
    <p>Holger<br>
    </p>
    <blockquote type=3D"cite"
cite=3D"mid:CALg-PKBPQPBMbNAxf-pF0OLbYs0exC7Vf2Sn3hNPUAJ7Q=3DmPtA@mail.gm=
ail.com">
      <div dir=3D"ltr"><img src=3D"http://s.bl-1.com/LYRKqCW.gif"
          moz-do-not-send=3D"true" width=3D"1" height=3D"1"></div>
    </blockquote>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------60DB514A57D39D69C484C220--

--xVmXS8SHkflbovkSkfLTptQAa9s8rItEa--

--tg8qX3C02YF5jBgNy8TcZjhxwzB8Ad0F5
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCX+w5DgUDAAAAAAAKCRCKV5yTsxlhuhLl
AJ0eCYgUhIgtsb0V0NypG6SHVzL6dgCeMnyGCOoOz1dffZiIiuA60XTyEQE=
=WVjX
-----END PGP SIGNATURE-----

--tg8qX3C02YF5jBgNy8TcZjhxwzB8Ad0F5--