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 1pFveU-0002Y6-7D for pgsql-committers@arkaria.postgresql.org; Thu, 12 Jan 2023 11:21:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFveS-0006rP-TN for pgsql-committers@arkaria.postgresql.org; Thu, 12 Jan 2023 11:21:44 +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 1pFveR-0006qu-Rf for pgsql-committers@lists.postgresql.org; Thu, 12 Jan 2023 11:21:44 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFveJ-0002bZ-WC for pgsql-committers@lists.postgresql.org; Thu, 12 Jan 2023 11:21:42 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 9DEF132009AF; Thu, 12 Jan 2023 06:21:31 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Thu, 12 Jan 2023 06:21:31 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; t=1673522491; x=1673608891; bh=e q4Uax38XOkNbAAhFdORvwricoECJAiZ8lN6cRnYiG0=; b=mRPQJTmuGBa/nJWSg ETDVhVHqtwpDlDEj3+T0c7Llp2AAxkaEXyyVDBmjZmOzuxGP4aC9tORA6z4czVIK 7OeogoPvrmZn0f/3XFnYBPArYotoOz6kS7mRktB0QZmDf6YlxAqtX8VgnX++mVEO LGEC22nCWTgfcBp3ZWXhH4gfxeRXKVWWp2yT/3MtFYEcXK61XfGqbnjihf/+tPYF JI9SwCQ6n54okL6Xh1KsqT4VlfPRr3sw0nSYFDjTBrXDCA7ADBXRa+PbMV7lxr+i ZNJIGZlA+wZ+l2CNEqFOdWnBonU63NbH/+0bXd5BWbO8mxwLUOOTyTUO2pN0SdBH gp1Fw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrleeigddviecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvvefukfggtggugfgjsehtkeertddttdejnecuhfhrohhmpeetlhhvrghr ohcujfgvrhhrvghrrgcuoegrlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhgqe enucggtffrrghtthgvrhhnpeeuiefgffeukedtgfetueeugfetvdeuhefgheelfeehleej leefffejtddtkedvjeenucffohhmrghinhepphhoshhtghhrrdgvshdpvghnthgvrhhprh hishgvuggsrdgtohhmnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghi lhhfrhhomheprghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 12 Jan 2023 06:21:30 -0500 (EST) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 546511D; Thu, 12 Jan 2023 12:17:46 +0100 (CET) Date: Thu, 12 Jan 2023 12:17:46 +0100 From: Alvaro Herrera To: Tomas Vondra Cc: pgsql-committers@lists.postgresql.org Subject: Re: pgsql: Sample postgres_fdw tables remotely during ANALYZE Message-ID: <20230112111746.uoowzww6lt2ypldg@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2022-Dec-30, Tomas Vondra wrote: > Sample postgres_fdw tables remotely during ANALYZE > > When collecting ANALYZE sample on foreign tables, postgres_fdw fetched > all rows and performed the sampling locally. For large tables this means > transferring and immediately discarding large amounts of data. > > This commit allows the sampling to be performed on the remote server, > transferring only the much smaller sample. The sampling is performed > using the built-in TABLESAMPLE methods (system, bernoulli) or random() > function, depending on the remote server version. > > Remote sampling can be enabled by analyze_sampling on the foreign server > and/or foreign table, with supported values 'off', 'auto', 'system', > 'bernoulli' and 'random'. The default value is 'auto' which uses either > 'bernoulli' (TABLESAMPLE method) or 'random' (for remote servers without > TABLESAMPLE support). You left out the Discussion line, so here's the link: https://postgr.es/m/151e835f-55d6-ddbc-b5b4-07ee606aba44@enterprisedb.com -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre)