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 1jiHUk-0001jh-Hj for pgsql-general@arkaria.postgresql.org; Mon, 08 Jun 2020 13:07:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jiHUh-0002Ma-0Z for pgsql-general@arkaria.postgresql.org; Mon, 08 Jun 2020 13:07:15 +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 1jiHUg-0002MT-MR for pgsql-general@lists.postgresql.org; Mon, 08 Jun 2020 13:07:14 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jiHUd-0006zI-9m for pgsql-general@lists.postgresql.org; Mon, 08 Jun 2020 13:07:14 +0000 Received: by mail-ot1-x332.google.com with SMTP id g5so13541244otg.6 for ; Mon, 08 Jun 2020 06:07:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=ytHauY66DbffNbVCVCgyLbbJhrqANfGd6ioN1FmMFYc=; b=FmUT/347TDdQb+6HQyMKDBnJPHEjFoeHu1xsh+m02YGtjhBkBjCT/D90VlWxaYc59X UFlJ/vgfRxW4Kl4uFDwFan0OFL7E1wJnCuVSx0WI+BlnbXhme6WxBTjNb6Mp9N5wqhgO Nz5cUmDAyC3iVmdF2SLRVslDp8kxWaz5IHFnrRW4orbRGORgtG1lHd27hJjQ51VKdyPS CIbWsN/g1ct3NYfeHZ/BX03KLOXAZ4vE/ReshnCM4Gd52YaZyBLVYd4aFYhuSnXzUCiq OQ9/2TMdihM37NWOjThZdZJRK6DBYVrkw9cI+yv7zIiSIVSH7Wxm7jTTY85vXKOnk0+B 9kaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=ytHauY66DbffNbVCVCgyLbbJhrqANfGd6ioN1FmMFYc=; b=TKNT0dp0FMI7k06ORqm1XzNKZVrJY5da5uvQisnf0IY7gb8KtP9mJvnRZ/mfq+wM0I SNgp4U9gOkGWFEk1bDR1KIeJO2DJeE7ZyqivThSHNh6jbVoJliWhh6EbYCp8W/2FYyX9 j3sbJu3d2rv3VoQ5wQDypRtfN9SfbXD7yflI6XK0XwLYaqCGfyW9BCSyZTeZNzuBZTvy TtVQA+hFGOJNx3+Y4QYI5NyXj9uuTxmKiSu8/RCZJqbwlgxK6Nu3ZugWukcAA+0kXqgO jDO5jJ4gHA3gsdBPzAhdqyzFTZZKrUyqeZmQwmqEnxDuujtIvAwsnYH9JKVYHgnHr8fr TJlg== X-Gm-Message-State: AOAM5313JWE1CNYO/NUlkNykeNiXiJRingWbkzPgM5HWFJE7GgiW/2oa A4JIWaIpku+a/vJe75EDLRQm3y1geYQWPw== X-Google-Smtp-Source: ABdhPJyDj9eoV1PmlF/qZM7JFznq3oRZXofdFwUggufiwllU/HUQ1EsUdH2X9o33RtTskmaM54+Ofw== X-Received: by 2002:a9d:6e84:: with SMTP id a4mr16863217otr.112.1591621626543; Mon, 08 Jun 2020 06:07:06 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id p9sm1515355ota.24.2020.06.08.06.07.05 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 08 Jun 2020 06:07:05 -0700 (PDT) Subject: Re: When to use PARTITION BY HASH? To: pgsql-general@lists.postgresql.org References: From: Ron Message-ID: <0592c44a-cd2b-da4b-4dea-2017ae41507a@gmail.com> Date: Mon, 8 Jun 2020 08:07:04 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.4.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------3FA00205BD3BED44BCAD86B2" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------3FA00205BD3BED44BCAD86B2 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 6/8/20 3:40 AM, Oleksandr Shulgin wrote: [snip] > I've found the original commit adding this feature in version 11: > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1aba8e651ac3e37e1d2d875842de1e0ed22a651e > It says: > > "Hash partitioning is useful when you want to partition a growing data > set evenly.  This can be useful to keep table sizes reasonable, which > makes maintenance operations such as VACUUM faster, or to enable > partition-wise join." How does hashed (meaning "randomly?) distribution of records make partition-wise joins more efficient? Or -- since I interpret that as having to do with "locality of data" -- am I misunderstanding the meaning of "partition-wise joins"? -- Angular momentum makes the world go 'round. --------------3FA00205BD3BED44BCAD86B2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 6/8/20 3:40 AM, Oleksandr Shulgin wrote:
[snip]
It says:

"Hash partitioning is useful when you want to partition a growing data
set evenly.  This can be useful to keep table sizes reasonable, which
makes maintenance operations such as VACUUM faster, or to enable
partition-wise join."

How does hashed (meaning "randomly?) distribution of records make partition-wise joins more efficient?

Or -- since I interpret that as having to do with "locality of data" -- am I misunderstanding the meaning of "partition-wise joins"?

--
Angular momentum makes the world go 'round.
--------------3FA00205BD3BED44BCAD86B2--