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 1tEUif-007h54-Pp for pgsql-performance@arkaria.postgresql.org; Fri, 22 Nov 2024 14:33:13 +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 1tEUid-0098M6-28 for pgsql-performance@arkaria.postgresql.org; Fri, 22 Nov 2024 14:33:11 +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 1tEUic-0098Lx-Np for pgsql-performance@lists.postgresql.org; Fri, 22 Nov 2024 14:33:10 +0000 Received: from mail-pl1-x631.google.com ([2607:f8b0:4864:20::631]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tEUiW-003IQI-MC for pgsql-performance@lists.postgresql.org; Fri, 22 Nov 2024 14:33:10 +0000 Received: by mail-pl1-x631.google.com with SMTP id d9443c01a7336-212008b0d6eso18690465ad.3 for ; Fri, 22 Nov 2024 06:33:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732285982; x=1732890782; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=8DHHDkLG8zduZsE0pXaCvaiihMWaH+/g5QQpS+o2PbU=; b=iuFhNRJwTrRNMBbUwQJaz/6kd6sgAbHgIeYhFvHsh3+cP6aQY/mYpmC8vOIHXHsnvt hHSx9nspJ45t+olxnZEBqgTngMGhFxjb5DCkHMQwnK7CeuaeAffvuzF9DoCafOrigvkv z60miaTYYSAGtAzZKGScHpExEK1rdjELs0ah+aGKyVQeO6SxmmJ6Ah51DdYptSai3/Im 5szL99mBlxJkwLMQ7t3PjXatiPBfKPx739aQ9SfJCjJwVVCOXGkC6zl7ceO4iJftcwW1 wnwvg0rgj5UD5R5IVyPZ8pdGypb7ULZte0dQx7ASBbFXImI8ywDNvjtKs14EE0C+vcGq G2RA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732285982; x=1732890782; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=8DHHDkLG8zduZsE0pXaCvaiihMWaH+/g5QQpS+o2PbU=; b=CdjUKcPrt1p6ksQfAlR7bwfbOi0DxawymJXvkclr5SHvPL9coFa+eteAA7sv8oHhrH UUfZ2v5zywTbK0ZU3PPNvbnT7wnkpxKwFS+DlwZbEmPN69hfd9DAVim1USDxSg8TdG8b TCRl4TGhBlmisdpIDkcFWhJfwrXUj+1Vwyl/XLTxtqJtkmzqlc7HOpYWJ63BbLHQjTEk 2deg+VwRtrOrnw+MjVzQ0FC1+YVguFk5mcE+gu7qmy9CAx1IBMcRILhimrw7EtyFWrLi Uw+H6u6YtBmRv8Hb3S9H1wMH3kpixgW2K2v+dY98bp+0/hSVhODEqIogkutANGyG49ya NCwQ== X-Forwarded-Encrypted: i=1; AJvYcCW07TTLj5LAXBx2dGX8bA9YLQ2kkxHL00LH5HabrhaaB28DuxlIWoy+s2u8mROMcDLVbqE+ldEoNOp5hzivT2OHgQ==@lists.postgresql.org X-Gm-Message-State: AOJu0YzzC2hIAUAk4FUfW6UluzpT9ZUqnHaivbm5Ysp2SxQ9unZSzehK UG/vWB02AlQ65YcNIdFg7whjJzAIX13sXfkU6LWNp3PiFQAuGOmR X-Gm-Gg: ASbGncuwmDoSDN3jnt7BNayoJy4ajcZGVxxjeCd/bXIAICbSH/M5+tkJLO5FmmqXdCH vjwmql7IJBAEW9H+2JAEgV1BwZ8HgADXFSA1aFoiXkFgfyZg51pPTkY8Zoqm7qc5e/hkx/sSXhw HUeR3xqDLufIpDlm+hOv0eLscL/2ZGgj5v5PKhfEDE4ey67HYikNtMUnnjZm+t7s+NR6o361CQT O0LYYHA1DosleuRE44EyIqkZy58bblScQX5soqv0ejr0IDYetqz4tKVzddypz7raqvO/BX6O3cy 8SDmqZgnkIjB7cVsRdFhgOOzB/Fe7A== X-Google-Smtp-Source: AGHT+IE5M8vQvwPkY23IKjN6UJWWTg54092JgJNZw7h6RPBZ7xD9rYPDBa9STNCuO53OPWZKgC/CgQ== X-Received: by 2002:a17:902:ce02:b0:20c:c086:4998 with SMTP id d9443c01a7336-2129f28d328mr40140245ad.55.1732285981550; Fri, 22 Nov 2024 06:33:01 -0800 (PST) Received: from [192.168.1.37] (cm-27-145-170-174.revip12.asianet.co.th. [27.145.170.174]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-2129dc203afsm16686605ad.223.2024.11.22.06.32.59 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 22 Nov 2024 06:33:00 -0800 (PST) Message-ID: <202aa06a-678c-430b-8f87-fb694b9703e8@gmail.com> Date: Fri, 22 Nov 2024 21:32:57 +0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Performance of Query 60 on TPC-DS Benchmark To: Ba Jinsheng , "pgsql-performance@lists.postgresql.org" References: Content-Language: en-US From: Andrei Lepikhov In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 22/11/2024 18:12, Ba Jinsheng wrote: > I think the key difference is that the patch disables the usage of Hash > Join, which incurs a worse performance. I see here a problem with a number of groups: when predicting it incorrectly, Postgres doesn't use the Memoize node. Disabling HashJoin puts NestLoop+Memoize at the place of the best path, which is chosen later. Unfortunately, we can't see a prediction on the number of groups in Memoize and can only guess the issue. -- regards, Andrei Lepikhov