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 1r3KQ9-0039Gq-Qw for pgsql-general@arkaria.postgresql.org; Wed, 15 Nov 2023 18:15:25 +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 1r3KQ8-001SZi-8C for pgsql-general@arkaria.postgresql.org; Wed, 15 Nov 2023 18:15:24 +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 1r3KQ7-001SZa-Tq for pgsql-general@lists.postgresql.org; Wed, 15 Nov 2023 18:15:23 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r3KQ5-006bLC-Pl for pgsql-general@lists.postgresql.org; Wed, 15 Nov 2023 18:15:23 +0000 Received: by mail-wm1-x332.google.com with SMTP id 5b1f17b1804b1-40853c639abso55598085e9.0 for ; Wed, 15 Nov 2023 10:15:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1700072120; x=1700676920; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=z0CGoxcGwC+vv8TvOki5vMmud/pnu8vMFBSJu7MNUyQ=; b=r9U+P8smRNS5INx8nW4LKVqUVStosbU3iyY0o0k9mcsN5dqmbHkyH/DGoePdL4NLhR 7wFB6MZHjUdiwQVhpEXAYWUV1V1QslfiyfZIfGjTLqc3Y3FYpeLnprTqk2/8tFWbZ8dK W0jMctr/ckl23MxUkKclClI9uoHig0hXs4EntSxxT8x3ZFLY7TnKVRTG9lN80Z+ICmSu eLTo/MfwwBzQJ88vuqcg1fpOKsSwdKmEwx48J5MQQ/K+pZInD2pJTbSrgt/Xk9IyuNB9 9946v6CnloSHN+L5eVD5HxZNPeRo8gS3EH8DmocS6wAtQg6PAhza8sIpPx/lPEDeMt3v v9fA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700072120; x=1700676920; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=z0CGoxcGwC+vv8TvOki5vMmud/pnu8vMFBSJu7MNUyQ=; b=jNteLTTGdky5ZV4zykTtisbT1+DgX4qjBuKvYAAHib6GcWJkW3n2SMCwEWGgjDm1e1 +z3YyO+fvNoAxezVFwI3gZCWqhJjDndl55HWb9QFuX0FK3FymJR+PhRe5igIF8w59+ZF ffYeaIHkftdrO7M6+XJoszYAcJU1UzS4aKMTH4Dz101ngWYgzaSnN/z/f7gLmtYqVBlj W0MaV1qU1WaP0nHnn3SrxPnCxF1uU0i+FRSzOMYQIIMcLoI5K2aiD7h0JVXAGXCtSJQC /mlRk6F6NW/0X4/VYerlQI4BYqbl9B2DmRSrJrOShUolmcW2q9u5C/57Awg2k4V3Y0Ne DngQ== X-Gm-Message-State: AOJu0YwdA+1aQXUO+oLY2nwqFxQCJWg4udO/ZG8D7QypL4qSOeH0XuFv 9nkGBRLzINz3C840/Ptc1y93Dg== X-Google-Smtp-Source: AGHT+IGSTEzZn7C2gI3D2vd/rhWxKpJXQzQBr3CriJP2mhl1wpUVM6pYnbGd5ERFwjcR6eQaU4BXuQ== X-Received: by 2002:adf:ea90:0:b0:32d:b051:9a29 with SMTP id s16-20020adfea90000000b0032db0519a29mr9411650wrm.2.1700072120460; Wed, 15 Nov 2023 10:15:20 -0800 (PST) Received: from localhost.localdomain (p50992f7c.dip0.t-ipconnect.de. [80.153.47.124]) by smtp.gmail.com with ESMTPSA id d1-20020adfe841000000b0032dba85ea1bsm11257067wrn.75.2023.11.15.10.15.19 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 15 Nov 2023 10:15:20 -0800 (PST) Message-ID: Subject: Re: Is "DISTINCT" making a diffence in: SELECT [DISTINCT] ... EXCEPT From: Laurenz Albe To: Dimitrios Apostolou , pgsql-general@lists.postgresql.org Date: Wed, 15 Nov 2023 19:15:18 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-11-15 at 10:57 +0100, Dimitrios Apostolou wrote: > SELECT [DISTINCT] ... EXCEPT ... >=20 > In this query I get the same results regardless of including DISTINCT or > not. But I get different query plans, I get an extra HashAggregate node > in the case of SELECT DISTINCT. Any idea why? The DISTINCT is superfluous, because EXCEPT already removes duplicate rows. However, the planner does not invest extra processing cycles to detect that you wrote a superfluous DISTINCT, and it does not remove it. As a consequence, you end up with a pointless extra execution plan node that does not achieve anything except slowing down the query. Remove the DISTINCT. Yours, Laurenz Albe