Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rFgoz-00BTzO-Rg for pgsql-general@arkaria.postgresql.org; Tue, 19 Dec 2023 20:36:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rFgoy-0051sy-HB for pgsql-general@arkaria.postgresql.org; Tue, 19 Dec 2023 20:36:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rFgoy-0051sq-6e for pgsql-general@lists.postgresql.org; Tue, 19 Dec 2023 20:36:08 +0000 Received: from mail-pl1-x62d.google.com ([2607:f8b0:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rFgos-00BDwR-GS for pgsql-general@lists.postgresql.org; Tue, 19 Dec 2023 20:36:06 +0000 Received: by mail-pl1-x62d.google.com with SMTP id d9443c01a7336-1d3e416f303so5900405ad.0 for ; Tue, 19 Dec 2023 12:36:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1703018161; x=1703622961; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=sXoDfQ6NXNawCgCxFLNEyik84O1w16lZSZMCqBm4ZxE=; b=BfKCGF3gxoV3NQdeRIJvtKtA1dFWLCYxH3E00qkGx5Vz3pIacEdp5v2bUpafi+sKFI 1dKnhyv15tt+6jS2h/xN2cwDBVu6FZWaQsPJe/KAWByBrUICG1L7+QaMifnKIwx+aFul TNzT1wsEwBrETtsEf6v6zTO64h7xIrkVZKsnKgIY3MfPM7d1N3flf1jsXOCA8WQ1iqdT b2TxK94vzAQeVLz2euszKrPhkfHNdSEJeLQBSxf3twgqTE8tkRzcog5/YwyJWT723NoZ Bqcy3zMJWPqS6ufEFdIeJz3nEYAEVGE002o9qCT5vndHRaQsWtkiHtc8gCpPggc2GEoU +qpg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1703018161; x=1703622961; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=sXoDfQ6NXNawCgCxFLNEyik84O1w16lZSZMCqBm4ZxE=; b=oaFl2oDZ6zz8lx3EWmMwsV0sH83qimHmI3jVrru/dUk6tmcOCh6DbGgeMMDXGom1sW LOOq+jTmpZtgk+ga7kjshSzd3kXDBZMUDNPuDt25Cbt6n+lBR/xtu2vSgAej6N58B9SC DzChu3wuxFKctl1x0cJzcHe+hxN/gUZI8iUiUd8kxoOTW8EIU2qKfIlMYnTeoMnX1tr8 tNpk6ByUU9V/pw5WAZZ2EkqDOqDlW8Bo4YP9KCGz0Gw1PvRuxfs6dNSJBrKIKtzFInnd 2xoDCTd79ZLsAtsOu666ShhCSC0Y57pcJAR3QD2K2wilVAKdzD0SbBZgrF7FoiDVu2ud gCLA== X-Gm-Message-State: AOJu0Yy0Asdsvy8JQCJ5LqPtDxyoSTT5NPT00ZGRvr3D1yNTtlByzYtr 2Ndtj66GsYbwJ87jWP7pnPMq30Cdzhw= X-Google-Smtp-Source: AGHT+IFtGUIuyVIFBwXHuNk1lFAxUkIrTseEblHSmE9au4T3GgXccr+fHXhgq/NEn6KifXBpwdV/oQ== X-Received: by 2002:a17:902:7b91:b0:1d0:6ffe:1e6e with SMTP id w17-20020a1709027b9100b001d06ffe1e6emr10371582pll.81.1703018160839; Tue, 19 Dec 2023 12:36:00 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:7895:390d:eca2:be1c? ([2601:681:5a00:f790:7895:390d:eca2:be1c]) by smtp.gmail.com with ESMTPSA id m2-20020a170902bb8200b001cfd2cb1907sm21441259pls.206.2023.12.19.12.36.00 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 19 Dec 2023 12:36:00 -0800 (PST) Message-ID: <926bcc4e-587d-4e83-bab1-273115654442@gmail.com> Date: Tue, 19 Dec 2023 13:35:59 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Read write performance check Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: From: Rob Sargent In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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.