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.
