pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Read write performance check
Date: Tue, 19 Dec 2023 13:35:59 -0700
Message-ID: <926bcc4e-587d-4e83-bab1-273115654442@gmail.com> (raw)
In-Reply-To: <CAB+=1TXHvvVfzOLibSQ9J9SazMvPxttVW__GsfT5kjajAD1iWQ@mail.gmail.com>
References: <CAB+=1TVWinj5Zo4K_=LsR74cafhhEo0Zh==sdybaQ+ZO5UtWJQ@mail.gmail.com>
	<CAG95seU-yoctWToM2a4a3N_GFztZF2htWRh30fM+1hwVi2nWeQ@mail.gmail.com>
	<CAB+=1TXHvvVfzOLibSQ9J9SazMvPxttVW__GsfT5kjajAD1iWQ@mail.gmail.com>

On 12/19/23 12:14, veem v wrote:
> Thank you for the confirmation.
>
>  So at first, we need to populate the base tables with the necessary 
> data (say 100million rows) with required skewness using random 
> functions to generate the variation in the values of different data 
> types. Then in case of row by row write/read test , we can traverse in 
> a cursor loop. and in case of batch write/insert , we need to traverse 
> in a bulk collect loop. Something like below and then this code can be 
> wrapped into a procedure and passed to the pgbench and executed from 
> there. Please correct me if I'm wrong.
>
> Also can you please guide how the batch(say batch size of ~1000) 
> Insert can be written ?
>
> -- Row by row write
>  FOR i IN 1..total_rows LOOP
>         data_row := (SELECT
>             md5(random()::TEXT),
>             floor(random() * 100)::INT,
>             random() * 1000::NUMERIC,
>             NOW()::TIMESTAMP
>         );
>         INSERT INTO BASE_TABLE(column1, column2, column3, column4)
>         VALUES (data_row.column1, data_row.column2, data_row.column3, 
> data_row.column4);
>     END LOOP;
>
> --Row by row read
> BEGIN
>     FOR i IN 1..total_rows LOOP
>         -- Row by row read
>         SELECT * INTO data_row FROM BASE_TABLE WHERE limit 1;
>     END LOOP;
> END;
>
This row by row is guaranteed to be slow if there's no index on the 100M 
rows
> -- Batch read
> BEGIN
>     -- Batch read
>     OPEN data_set FOR SELECT * FROM BASE_TABLE LIMIT total_rows;
>     CLOSE data_set;
> END;
>
Does this batch read in the entire 100M row table? And some suspicious 
syntax

PS: Notice that top posting is frowned upon on this list.






view thread (12+ messages)  latest in thread

Message-ID: <926bcc4e-587d-4e83-bab1-273115654442@gmail.com>
Permalink:  ../926bcc4e-587d-4e83-bab1-273115654442@gmail.com/
Also on:    postgresql.org/message-id/926bcc4e-587d-4e83-bab1-273115654442@gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: robjsargent@gmail.com
  Subject: Re: Read write performance check
  In-Reply-To: <926bcc4e-587d-4e83-bab1-273115654442@gmail.com>
  References: <CAB+=1TVWinj5Zo4K_=LsR74cafhhEo0Zh==sdybaQ+ZO5UtWJQ@mail.gmail.com> <CAG95seU-yoctWToM2a4a3N_GFztZF2htWRh30fM+1hwVi2nWeQ@mail.gmail.com> <CAB+=1TXHvvVfzOLibSQ9J9SazMvPxttVW__GsfT5kjajAD1iWQ@mail.gmail.com> <926bcc4e-587d-4e83-bab1-273115654442@gmail.com>

* 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