Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6yef-0006zY-5k for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Jun 2023 19:17:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q6ydf-000230-RO for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Jun 2023 19:16:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6ydf-00022p-Dm for pgsql-hackers@lists.postgresql.org; Wed, 07 Jun 2023 19:16:11 +0000 Received: from mail-oa1-x2c.google.com ([2001:4860:4864:20::2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q6ydX-000scl-RD for pgsql-hackers@postgresql.org; Wed, 07 Jun 2023 19:16:10 +0000 Received: by mail-oa1-x2c.google.com with SMTP id 586e51a60fabf-19f30256921so8534218fac.1 for ; Wed, 07 Jun 2023 12:16:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=neon-tech.20221208.gappssmtp.com; s=20221208; t=1686165362; x=1688757362; h=in-reply-to:references:message-id:from:to:subject:date :content-transfer-encoding:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=4HNsiT9l2TcjlxbPPHlL1/UylNJwvsm3ADVEME+v+kA=; b=IRiJLd9skGiVW2TgK0E76skqPYACZPZTxhwpOQHy7uJJMsSf8h5XmWlbWznGCdUxT5 LdpoLOhw1xiIA/rW/0AU2eURfIJfbc7id+sijgcvetcB+wZyw0JqtMCkNFTfgJbZ04F7 2kFtmk83nLBYLT5mFS1/a8dvqVrhjdE4Wh1dUflHnnx5QqijXsWX8pAqPE5x/HypkPnn m8q9IXbIyx6nyb2++cXuIdrF4pqjp9HYzogwqwxuKCj64d9QwR9cXxA06jfsNZV09JfT Zsq3TV4jJfWHUNEQTl48MwcrjxfDyQexyR+nvgWfM7tbdzHbjAYl9D7pclIDGHfaXYGb Fx3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686165362; x=1688757362; h=in-reply-to:references:message-id:from:to:subject:date :content-transfer-encoding:mime-version:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=4HNsiT9l2TcjlxbPPHlL1/UylNJwvsm3ADVEME+v+kA=; b=b/Dsf6vfuJrT07nzVLE+AN7cLs+xyNW6zGFDvfHYGhaZgQp+LOwDJkL0sSyJYXpCiB mV4s9cUp2F3XIF4H1B2zMBtfQ0C+4R/eRafP2T5gAlUkIont5+Gj1Tx/n/2j7zSdSo0V 5OL4aVeMT2IkBStFphhyBSmECuawJgpuEgFU5T75j+uSezDUJQs6wzsWAvBl+UfX1fWK egGMwnoCQBIVy1ahoAaJh1bos9vxANjhMzB2nBvqCwoqs1NscrLbDOUvaJEG+w0xtlCn qcZgB7SMx9SxQXfstz4PTiNEFAZTukqDP/BQudAnZBaa9cPx010rbSCQBmkrEUJdh8b7 7wPg== X-Gm-Message-State: AC+VfDxN65V6Dgb4ou1KEDcPpffhhZ8oXapb1YfNatgIi5AXqnP/cDKw 0E2ZpnlW7QoufJt/Xmov/9LX6A== X-Google-Smtp-Source: ACHHUZ6VQC5fNerC1/xSyFDVnifXExojpOFi2wRd1Ej8VVbjkhB0kCqqtxW0BRcBKueK3Hr76Pxm1A== X-Received: by 2002:a05:6870:4e4:b0:19e:9281:94e1 with SMTP id u36-20020a05687004e400b0019e928194e1mr6247034oam.43.1686165361866; Wed, 07 Jun 2023 12:16:01 -0700 (PDT) Received: from localhost ([2607:fb90:e696:c1dc:3996:e070:71ee:42fb]) by smtp.gmail.com with ESMTPSA id e7-20020a056870944700b00172428894e0sm6218192oal.28.2023.06.07.12.16.00 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 07 Jun 2023 12:16:01 -0700 (PDT) Mime-Version: 1.0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=UTF-8 Date: Wed, 07 Jun 2023 14:16:00 -0500 Subject: Re: Use COPY for populating all pgbench tables To: "Tristan Partin" , From: "Tristan Partin" Message-Id: X-Mailer: aerc 0.15.2-33-gedd4752268b2 References: In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue May 23, 2023 at 12:33 PM CDT, Tristan Partin wrote: > I wanted to come with benchmarks, but unfortunately I won't have them > until next month. I can follow-up in a future email. I finally got around to benchmarking. master: $ ./build/src/bin/pgbench/pgbench -i -s 500 CONNECTION_STRING dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 50000000 of 50000000 tuples (100%) done (elapsed 260.93 s, remaining 0.00 s= )) vacuuming... creating primary keys... done in 1414.26 s (drop tables 0.20 s, create tables 0.82 s, client-side ge= nerate 1280.43 s, vacuum 2.55 s, primary keys 130.25 s). patchset: $ ./build/src/bin/pgbench/pgbench -i -s 500 CONNECTION_STRING dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 50000000 of 50000000 tuples (100%) of pgbench_accounts done (elapsed 243.82= s, remaining 0.00 s)) vacuuming... creating primary keys... done in 375.66 s (drop tables 0.14 s, create tables 0.73 s, client-side gen= erate 246.27 s, vacuum 2.77 s, primary keys 125.75 s). I am located in Austin, Texas. The server was located in Ireland. Just wanted to put some distance between the client and server. To summarize, that is about an 80% reduction in client-side data generation times. Note that I used Neon to collect these results. --=20 Tristan Partin Neon (https://neon.tech)