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 1qbT6r-00C8UM-NM for pgsql-general@arkaria.postgresql.org; Wed, 30 Aug 2023 21:52:21 +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 1qbT6q-002Jyl-I9 for pgsql-general@arkaria.postgresql.org; Wed, 30 Aug 2023 21:52:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qbT6q-002Jyb-50 for pgsql-general@lists.postgresql.org; Wed, 30 Aug 2023 21:52:19 +0000 Received: from mout.gmx.net ([212.227.17.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qbT6m-0020ey-50 for pgsql-general@lists.postgresql.org; Wed, 30 Aug 2023 21:52:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1693432333; x=1694037133; i=jimis@gmx.net; bh=pUYjkjQnOfTPPL94xBjNvsT6rS+O5OmWjBZCDBL8vEU=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:In-Reply-To:References; b=uc8mB5P+w8qxqpWzbAOCLPshed8DbhsPkZ6LLvpLiu16Ur2wKhLEOeBQfOr3RldlI134wkX dcAsiFswXoC8D0vYoy6OaaIQ0Koi2GdXXS81GFmUzI/HWD+WLhPkkoc79nboymTGC5mc9AZ+W vvw007DZVevjKX8wGUlvTC4+hbzB688+BMasBH+eAImIpFXIDFJQlklAYDkKrKHHJ29ZLCtIs nrfxkZTzdxf4ClxOBt7WrUTZzlSt+boWfHLvqjQkDZibyHYRGFlxyAi98ofOuYGPy77ZJ7Mcf gc0wbMG/Dj/KAoBsADNTKArjfjwTihB/EI4veQFYIg6Ea1rEKDNA== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.65] ([185.55.107.82]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1Mw9UK-1pjVZh34Jo-00s5MY; Wed, 30 Aug 2023 23:52:13 +0200 Date: Wed, 30 Aug 2023 23:52:12 +0200 (CEST) From: Dimitrios Apostolou To: David Rowley cc: pgsql-general@lists.postgresql.org Subject: Re: Partitionwise JOIN scanning all partitions, even unneeded ones In-Reply-To: Message-ID: References: <704abd8e-5b3e-f946-fb7c-58d39eb1268e@gmx.net> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:UYTgDmU2a6DC0KusQGavRY7O3b6tgX+zjS93c96Y9v0tmgHlqLV +u1PV1yfkt4oRT1ROskC9mkVMmfMZGBz3K/NjWSAbI8AgrwofaNLjBN6s+U9ef91jmGWHo5 Z5hc0UzKyjZATheEYzjQawdrLZIkMSPUiplWrnDayv/P3xGTu9rHtHWlyiOgl2C3EclMlmE TKtJhaKttR0TLnxIMMLOQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:lCw7v0VZqk0=;0m4IiOf05jF2DkqPlBlVwJLD4nQ +hPIwF5TexjUnO8zJ1KUTvt4Gs9vV95HpijLkG2kmaXU8vgi0jVon/pa59xY8af68GUvwYLE0 jae2hIKAIsnKpGRSOmCKUV6X70OM2ZyQO0H22Uf3g/JYs/fdNd6CIzgxh7ga2wBL5uDoFCHi+ Rf8scfCLSi3Tal6c3iMPK/gz9ifLpmk6xluTCGAxuYJ9eFztuCyYPZwXaA8Gc7ITPaBzpNX02 uCjE/hX1i8zhNttt85DUOca+KaXLoEUO1VF2OABiUNEpNZQPAYV8CEi+S+Fg1kysZptZKY6Ze rHqH41nmtcrFzrAOZ1+0M8Rl+yMfZ79e1mk0nq+9iPBwoAoMQEwXKDetvGUhbWpG0Bdv7C1RO oV3707SrtrmcQX/W6BFKJe3aOwa+OKPyzxeiE1C165cZf52sfypbT+ZNy5ddodHiMc+eXl/QP 6K+0LXlfVQdfemaAW3vxqxhhwc+sHrR9iFUDHRfxQ/NGnD6mPLvT4AIwHKkJFAObl7th/4ybm rpeRunMR2HQcxpz75kBuhsE85m6jQLjfI0jMtEOrKVYBUQ8m+82ddGhV0hOI7JZhSKM7UhhNS hxsS6layVYq10Oa1S8GGcISCFXPWhDjPhRojmbrfdR6E0iWio5C2XvBpENGTg27O9nR4lZuYP XWGopaTBxfRnLwFkkAZowyz2jnpfq5E09yxTic7Q773nWCccfNqXtOUGDJLnyqkdAQ08+GYuY 8cWsLIFxNbvcgtcoRczPXq/IQekdWze2MZFbImbNOYymCZD6inqBq70gel8/FiZJGz+C82FuI LeZd8hkmBJ+sdUQLT87LQ9RRTWXJKYCt29TCf7v6y8BImwQszibPFxlRzjA9yNEoFNTZp8tjU befLd9V7kjFBQXY5Yx1VkxYd2hh7DKJ2wQ+HN88OIYmn8WtV5HlCKckScL/WgD8nmmIfZ8+ij LiaPmQ== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Thank you for the clear explanation, and I hope the missing optimisation gets implemented sooner rather than later. Maybe the query planner should consider the missing optimisation and ban *hash* partitionwise joins. Indeed I verified that disabling hash join fixed the situation, with both queries taking almost the same time. Great! In the meantime, I'm considering disabling hash joins globally, as I've had issues with them before (excessive I/O slowing causing much slower execution than merge join, see [1] for your answer to my question then :). Do you think that would save me from other problems I'm not aware of, given the huge size of the table I'm querying? [1] https://www.postgresql.org/message-id/CAApHDvpPVydoNkEqLyBSbjWq8kq8M7Y= WdkA44rTeA2MNaO3jsw@mail.gmail.com Regards, Dimitris