Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1naeq4-0005h0-DG for pgsql-general@arkaria.postgresql.org; Sat, 02 Apr 2022 14:34:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1naeq2-0004uP-Qm for pgsql-general@arkaria.postgresql.org; Sat, 02 Apr 2022 14:34:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1naeq2-0004uG-EO for pgsql-general@lists.postgresql.org; Sat, 02 Apr 2022 14:34:50 +0000 Received: from mail-pg1-x52f.google.com ([2607:f8b0:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1naeq0-0002BM-2X for pgsql-general@lists.postgresql.org; Sat, 02 Apr 2022 14:34:49 +0000 Received: by mail-pg1-x52f.google.com with SMTP id o13so4565351pgc.12 for ; Sat, 02 Apr 2022 07:34:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=96bmIkgC4ccLYtMXpyocpawaToillIdFWOxHl3aMXEg=; b=knofJURbU89dzqT1T5Y033z8FznNevke8qH1XXYhByeI+smQ03w12scyfDTwaeHLN7 8dTk3IPoWA5BRvBoWnrGCkTejt1QWMh+wgwA1YniNjyJHTJUUZuiJw1991xYFaEaBUFZ Gi8OiKFDkDU1gDRzPpThUCBy4e9IV8qHPGbsvA2Fm1Qn94ETyc1WNjgDzbRG6wip/ngk NJEHJUTgptYXkxtXVsb57qa1oEgwbjgAD4Rc6pA75rI5eGVsar6NdOep64xsCEEl3/4P xB7slMXQFw9QCmbC8A+sHLeGFGUO3fjmHc+dzyfsfbymR+XZ521BwrR2jOswN+gjdKsQ zudg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=96bmIkgC4ccLYtMXpyocpawaToillIdFWOxHl3aMXEg=; b=RqwgAJQNRAssINk7LCZUiRRF/5nUsMQIn6KOjfTnpvA4/d9F5Tq1WqCGwJ1P3kA5uk x/ot89lBNqRpBDhp0NG2VTF1xkzgF3rTcsOgrTeXe8q+1yemcPtew1c/A1l83kPwdOys UPiFpSfXXXc+mRwL13o0MIqYt/4no2yskvOeYqbWltXgEkV4/BRqlXXk4tbXuY2XWCur eI1rvWg6R9PtBDN26pnmhjxuZWBc+FA6qioDLPWcF8AtkDYpUmASePV7Mtql10LhF9nV pvoX3CqkewGtXZmlfSOd9Pc1DEF2y4txmbHGssz8ZiJpMlzcqMnd+rVPYgAPSfa/e+J6 /aPA== X-Gm-Message-State: AOAM532UXC4sIfFo63iEunM8YULLlAXssBomS3NiB96KIuZ552JaU3ZM hBbBhqToaCOI2fxCY0SoIYJnHrmnBrVy3rh6WUE= X-Google-Smtp-Source: ABdhPJxXVU2OuZash01OaqNfU9uFNBPav+jpr991LOg7ZJl5y4/bi4RMjDW2Tm9MNn0hOZWb/aiA9+2eBhf/UlL5fC4= X-Received: by 2002:a63:c24:0:b0:382:2a04:3dbe with SMTP id b36-20020a630c24000000b003822a043dbemr18980480pgl.158.1648910086955; Sat, 02 Apr 2022 07:34:46 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Benedict Holland Date: Sat, 2 Apr 2022 10:34:34 -0400 Message-ID: Subject: Re: Re: How long does iteration over 4-5 million rows usually take? To: Shaozhong SHI Cc: Karsten Hilbert , Rob Sargent , Ron , pgsql-general Content-Type: multipart/alternative; boundary="000000000000ffe81e05dbaccb57" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ffe81e05dbaccb57 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable For work planning, create a temporary table, copy from a generated data set, and test it out. It really depends. Normally, you don't iterate in SQL since rows are assumed to be independent. You think about SQL in terms of sets and let application code handle the data transfers. It also really depends on the size of your tables, indexing, joins, and more. Basically, there isn't really an answer but it isnt hard to test. Thanks, Ben On Sat, Apr 2, 2022, 10:16 AM Shaozhong SHI wrote: > Thanks, Karsten, > > I would like the information to work planning purpose. > > Regards, > > David > > On Sat, 2 Apr 2022 at 14:47, Karsten Hilbert > wrote: > >> > > On Apr 1, 2022, at 10:18 PM, Ron wrote: >> > > >> > > =EF=BB=BF On 4/1/22 20:34, Shaozhong SHI wrote: >> > >> >> > >> I have a script running to iterate over 4-5 million rows. It keeps >> showing up in red in PgAdmin. It remains active. >> > >> >> > >> How long does iteration over 4-5 million rows usually take? >> > >> > 4-5 million times as long as it takes to do one iteration ( if you=E2= =80=99re >> doing it correctly) >> >> I may not take quite that long because setup/teardown times might not be >> needed for each iteration. >> >> Best, >> Karsten >> >> >> --000000000000ffe81e05dbaccb57 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
For work planning, create a temporary table, copy from a = generated data set, and test it out. It really depends. Normally, you don&#= 39;t iterate in SQL since rows are assumed to be independent. You think abo= ut SQL in terms of sets and let application code handle the data transfers.= It also really depends on the size of your tables, indexing, joins, and mo= re. Basically, there isn't really an answer but it isnt hard to test.
Thanks,
= Ben


On Sat, Apr 2, 2022, 10:16 AM Shao= zhong SHI <shishaozhong@gmail.= com> wrote:
Thanks, Karsten,

I would like the information to work p= lanning purpose.

Regards,

David

On Sat, 2 Apr 2022 at 14:47, Karsten Hilbert <Karsten.H= ilbert@gmx.net> wrote:
> > On Apr 1, 2022, at 10:18 PM, Ron <ronljohns= onjr@gmail.com> wrote:
> >
> > =EF=BB=BF On 4/1/22 20:34, Shaozhong SHI wrote:
> >>
> >> I have a script running to iterate over 4-5 million rows.=C2= =A0 It keeps showing up in red in PgAdmin.=C2=A0 It remains active.
> >>
> >> How long does iteration over 4-5 million rows usually take? >
> 4-5 million times as long as it takes to do one iteration ( if you=E2= =80=99re doing it correctly)

I may not take quite that long because setup/teardown times might not be ne= eded for each iteration.

Best,
Karsten


--000000000000ffe81e05dbaccb57--