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 1t0xWO-00DzRn-74 for pgsql-bugs@arkaria.postgresql.org; Wed, 16 Oct 2024 06:28:36 +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 1t0xWL-00Galk-N7 for pgsql-bugs@arkaria.postgresql.org; Wed, 16 Oct 2024 06:28:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0xWL-00Gak7-EC for pgsql-bugs@lists.postgresql.org; Wed, 16 Oct 2024 06:28:33 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0xWJ-0018Dw-62 for pgsql-bugs@lists.postgresql.org; Wed, 16 Oct 2024 06:28:32 +0000 Received: by mail-pj1-x1036.google.com with SMTP id 98e67ed59e1d1-2e2ed59a35eso4641069a91.0 for ; Tue, 15 Oct 2024 23:28:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729060110; x=1729664910; 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=LH24tZZ3b2zkeWZt21NfaxuWnz3t9P50BvH3p6mNcsQ=; b=ZTzlK1eCZOGlV1CdjbiNGLcgqjVsFHhtlUz+OAW0B967p+Oc3IzDYQpO+6zbYTkuxv wQgsnoAo0DFLZhVIH1WIb3Y4D9fXLGCtwpLk0gRNbaQiXEjXjpxQUn7KvY5tMwOfYv2N Obon5sbxxTYEEbl543hegwlDoiSKJfQR8vDfh3uN6X0RWHYEOkzvZ/DCHZQNmRkL9KMd bzMLDwzxeFP5RCMjfYr3qMNIY+thYadC0vSosTuxOxxiLOtvHDiMkQlfPMQR5MvYyU8p 9uVlWrfJLShv2BCPD/nB3RsfblyVFceYWcjTfdimMX0orZ8mQnTWe6rcA457L5ouyOH+ +oZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729060110; x=1729664910; 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=LH24tZZ3b2zkeWZt21NfaxuWnz3t9P50BvH3p6mNcsQ=; b=Bk5uAv1vUvkXcr4FP0GaO98eMEUB6IstnSnKLDQuygFkuPD68sbgElHigP+ht+JLZe IT6dsBrmu4qwtFqTZ09Z6pnCQVAgS22f0cVHZ7BbtvhHDJcHfR2LO0bogsorE3IElRsG yNZhIzzrtjXh1sUQDKqYLTKLF1ZxZ7M0dskphh44LPElUX17/j2S3wi7iHuxuv7aPaYi h9Wp8gXH9AF4VSBxecuJsDfEiQnv2exrvXEu9wIZa/dGqjLmG46xPsJEEOR+7cJrEl3V g7BhK37L1KOzBxzsIobF3ASdGjPwstm0+N/0N7P71VvRLY5fquHZrAOBEKrhrOiXMudB 7W3Q== X-Forwarded-Encrypted: i=1; AJvYcCWkDOcmeNnCVdFZd/kZGAobcgSGsI9w1dXaXlyEVC7lOw813FBOj2sJk5LY0sPBZxMVoxGR7O6cRG7w@lists.postgresql.org X-Gm-Message-State: AOJu0YzBFf9eu+eOiAuttn5mK2FVNbVF7ABVwZBG9gn/rAY7tn4Nqu7L fqyJxk5aCY3tVXDzt9omlUvFiqcRjpMV3d15RP/8e2RxcOKIRK2f X-Google-Smtp-Source: AGHT+IHDi40+olpAEdJQ+VM4adpeyLTiBViELyADbdtXMxNx+Ce04L2n7mSEZDPeMTdOiYNkW8rB4A== X-Received: by 2002:a17:90b:17c2:b0:2e2:dd25:9b00 with SMTP id 98e67ed59e1d1-2e3ab821dc7mr3447101a91.22.1729060109829; Tue, 15 Oct 2024 23:28:29 -0700 (PDT) Received: from [10.10.20.104] (node-thj.pool-101-109.dynamic.totinternet.net. [101.109.149.71]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-2e392f06a89sm3177913a91.34.2024.10.15.23.28.27 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 15 Oct 2024 23:28:28 -0700 (PDT) Message-ID: <44b42d35-2283-4a7f-aa2d-a9abe2dcf1b6@gmail.com> Date: Wed, 16 Oct 2024 13:28:25 +0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Performance Issue on Query 18 of TPC-H Benchmark To: Ba Jinsheng , "pgsql-bugs@lists.postgresql.org" References: Content-Language: en-AU 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 10/16/24 01:28, Ba Jinsheng wrote: > The major differerence between both query plans is the first one has > additional *SORT*. I believe the second query plan is more efficient. > Similar to my last report, perhaps we can optimize code to enable it. I would like to know if you can improve that case by switching from the sorted group to a hashed one. I see huge underestimation because of the HAVING clause on an aggregate. It would be interesting to correct the prediction and observe what will happen. Can you reproduce the same query using the SQL server? It would highlight some techniques Postgres has not adopted yet. -- regards, Andrei Lepikhov