Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e5BaV-0000Zm-40 for pgsql-admin@arkaria.postgresql.org; Thu, 19 Oct 2017 14:14:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e5BaT-0006pV-LO for pgsql-admin@arkaria.postgresql.org; Thu, 19 Oct 2017 14:14:17 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e5BaT-0006p9-2M for pgsql-admin@postgresql.org; Thu, 19 Oct 2017 14:14:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e5BaQ-0002MM-3J for pgsql-admin@postgresql.org; Thu, 19 Oct 2017 14:14:15 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id v9JEE9vP030524; Thu, 19 Oct 2017 10:14:09 -0400 From: Tom Lane To: "Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" cc: "pgsql-admin@postgresql.org" Subject: Re: question on hash joins In-reply-to: References: <60286BB5-0A38-42C8-A673-5289CA6D2354@nasa.gov> <63F14C4C-1518-4EAA-9EBE-87222988F073@nasa.gov> <22675.1508361426@sss.pgh.pa.us> Comments: In-reply-to "Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" message dated "Thu, 19 Oct 2017 13:09:42 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <30522.1508422449.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 19 Oct 2017 10:14:09 -0400 Message-ID: <30523.1508422449@sss.pgh.pa.us> List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org "Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" writes: > Sorry if I am being dense, but I still have a question=E2=80=A6 > Is it possible for me to estimate the size of the hash and a value for=20 > the temp_file_limit setting using information in the explain plan? Well, it'd be (row_overhead + data_width) * number_of_rows. Poking around in the source code, it looks like the row_overhead in a tuplestore temp file is 10 bytes (can be more if you have nulls in the data). Your example seemed to be storing one bigint column, so data_width is 8 bytes. data_width can be a fairly squishy thing to estimate if the data being passed through the join involves variable- width columns, but the planner's number is usually an OK place to start. > For example, one possibility is that the hash contains the entire tuple f= or each > matching row. No, it's just the columns that need to be used in or passed through the join. If you want to be clear about this you can use EXPLAIN VERBOSE and check what columns are emitted by the plan node just below the hash. regards, tom lane --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin