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.
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