Re: Read write performance check

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.