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 1t9cQV-00BJyl-IU for pgsql-general@arkaria.postgresql.org; Sat, 09 Nov 2024 03:46:19 +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 1t9cQS-00Ffvp-DE for pgsql-general@arkaria.postgresql.org; Sat, 09 Nov 2024 03:46:16 +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 1t9cQR-00Ffvh-Tx for pgsql-general@lists.postgresql.org; Sat, 09 Nov 2024 03:46:16 +0000 Received: from mail-lj1-x230.google.com ([2a00:1450:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t9cQO-000xPq-AY for pgsql-general@lists.postgresql.org; Sat, 09 Nov 2024 03:46:15 +0000 Received: by mail-lj1-x230.google.com with SMTP id 38308e7fff4ca-2fb5be4381dso23925001fa.2 for ; Fri, 08 Nov 2024 19:46:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731123971; x=1731728771; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=0DSuXCKA1Xz8d10Fg6+UAJVC1YoYy3NSMb9VZwPHwmE=; b=dPZSaDC6PU8P4lx1tB2RzVNGjJMYt6aXuJC4xeef/qoJnorzKLsuy8X+5R14V0JnGa LJOFA7yso5HLn38AA8Pw6gux1DfnfsDZ2nFqeSV8XJXiOxkw5IQNOy2sq9LfTUhtSFJY 5tU52KBj8ZpFqAJQJdIu4E5+QnhUmq69dNPRgmQsQlIA/oxCEaK02B8DluHErd67uLfW WQkCTePSApCIy2Gt4JwtXhB6VWviUX7O+udxihBBg+oBj9OoWrzzSh1deTyoyS1/CAXI givbv9r6RJISB8Igy1od8pQL+6OhSOfYqdXfpODbjltyF9oYR14PKlqxtS7Q8OfAi5Tx aSIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731123971; x=1731728771; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=0DSuXCKA1Xz8d10Fg6+UAJVC1YoYy3NSMb9VZwPHwmE=; b=iHUsybSH40Xoal6om8VcTnxR/riw5eKTHSkiG1HHB93Tu4Qh4zmxKMtGu6YIxDsm1q 8KjTqVGR3QaxfaXUVgS/WUXe65ZU54W9zl3IWiVDPFDyiK7X19S1Ai4Kzbfe14U6gaef ZStN2iz7d93/waIRvyhYb1AnGVlVRdb2VAJPeveojwm9oaU/4bD3z1CX5fXlkxyaUhPP xuYv0lHg6kfHwoF5t1dO5J1/woTs5lDzuwRE81UGISKURfa57V45c/Bgnx++aMsxIXIo DP/4HaC2/0zaGnmNGu2urY38/GpoBLzoANIkvrv0TdRfL/ll/qGlVrfoduLWbbTWg/RK gtSA== X-Forwarded-Encrypted: i=1; AJvYcCVg7CLawLvpT6xRtBQ6XNgWksLbHket2iB8yf/ElKX5wvO+suMUSzyqfcSLYScwqhEhD4V3x5w1SCK6IxWg@lists.postgresql.org X-Gm-Message-State: AOJu0YwIJYg4hURaXwc4VahTbsGKkEPBvEkuco9vI1vFp+cYfPHRqfU1 ZxpaQ4TyRpljWDMjmtuTYV3pi6I/zAliPYnCy6zYfW4iREM6WVNQ36WnDGJdMuqQGKCE8EIQmP8 +JLpjl1CbVdPgJNOftOL8lKptgEw= X-Google-Smtp-Source: AGHT+IGLvqK29M44UDhbGqjOfXgHXoubs9BGcjsFlbhJu5HasEdgAm2PNoCmtw7Not6h/k7A0PWNTQMi/AECZwH/S6o= X-Received: by 2002:a05:651c:2122:b0:2fb:5e64:327e with SMTP id 38308e7fff4ca-2ff20288ef5mr28733181fa.33.1731123971193; Fri, 08 Nov 2024 19:46:11 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: ravi k Date: Sat, 9 Nov 2024 09:15:57 +0530 Message-ID: Subject: Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16 To: David Mullineux Cc: Ramakrishna m , pgsql-general Content-Type: multipart/alternative; boundary="0000000000005d80fc062672b5b6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005d80fc062672b5b6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Sorry, it was typo. Bind variable is bigint only. Thanks On Fri, 8 Nov, 2024, 7:09=E2=80=AFpm David Mullineux, wr= ote: > Just spotted a potential problem. The indexed column is a bigint. Are you= , > in your prepared statement passing a string or a big int ? > I notice your plan is doing an implicit type conversion when you run it > manually. > Sometimes the wrong type will make it not use the index. > > On Fri, 8 Nov 2024, 03:07 ravi k, wrote: > >> Hi , >> >> Thanks for the suggestions. >> >> Two more observations: >> >> 1) no sequence scan noticed from pg_stat_user_tables ( hope stats are >> accurate in postgres 16) if parameter sniffing happens the possibility o= f >> going to sequence scan is more right. >> >> 2) no blockings or IO issue during the time. >> >> 3) even with limit clause if touch all partitions also it could have bee= n >> completed in milliseconds as this is just one record. >> >> 4) auto_explain in prod we cannot enable as this is expensive and with >> high TPS we may face latency issues and lower environment this issue can= not >> be reproduced,( this is happening out of Million one case) >> >> This looks puzzle to us, just in case anyone experianced pls share your >> experience. >> >> Regards, >> Ravi >> >> On Thu, 7 Nov, 2024, 3:41=E2=80=AFam David Mullineux, = wrote: >> >>> It might be worth eliminating the use of cached plans here. Is your app >>> using prepared statements at all? >>> Point is that if the optimizer sees the same prepared query , 5 times, >>> the it locks the plan that it found at that time. This is a good trade= off >>> as it avoids costly planning-time for repetitive queries. But if you ar= e >>> manually querying, the a custom plan will be generated anew. >>> A quick analyze of the table should reset the stats and invalidate any >>> cached plans. >>> This may not be your problem just worth eliminating it from the list o= f >>> potential causes. >>> >>> On Wed, 6 Nov 2024, 17:14 Ramakrishna m, wrote: >>> >>>> Hi Team, >>>> >>>> One of the queries, which retrieves a single record from a table with >>>> 16 hash partitions, is taking more than 10 seconds to execute. In cont= rast, >>>> when we run the same query manually, it completes within milliseconds.= This >>>> issue is causing exhaustion of the application pools. Do we have any b= ugs >>>> in postgrs16 hash partitions? Please find the attached log, table, and >>>> execution plan. >>>> >>>> size of the each partitions : 300GB >>>> Index Size : 12GB >>>> >>>> Postgres Version : 16.x >>>> Shared Buffers : 75 GB >>>> Effective_cache : 175 GB >>>> Work _mem : 4MB >>>> Max_connections : 3000 >>>> >>>> OS : Ubuntu 22.04 >>>> Ram : 384 GB >>>> CPU : 64 >>>> >>>> Please let us know if you need any further information or if there are >>>> additional details required. >>>> >>>> >>>> Regards, >>>> Ram. >>>> >>> --0000000000005d80fc062672b5b6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Sorry, it was typo. Bind variable is bigint only.

Thanks=C2=A0

On Fri, 8 Nov, 2024= , 7:09=E2=80=AFpm David Mullineux, <= dmullx@gmail.com> wrote:
Just spotted a potential problem. The indexed column is a bi= gint. Are you, in your prepared statement passing a string or a big int ?I notice your plan is doing an implicit type conversion whe= n you run it manually.
Sometimes the wrong type will= make it not use the index.

On Fri, 8 Nov 2024, 03:07 ravi k, <rav= isql09@gmail.com> wrote:
Hi ,

Thanks for= the suggestions.

Two mo= re observations:

1) no s= equence scan noticed from pg_stat_user_tables ( hope stats are accurate in = postgres 16) if parameter sniffing happens the possibility of going to=C2= =A0 sequence scan is more right.

2) no blockings or IO issue during the time.

3) even with limit clause if touch all parti= tions also it could have been completed in milliseconds as this is just one= record.

4) auto_explain= in prod we cannot enable as this is expensive and with high TPS we may fac= e latency issues and lower environment this issue cannot be reproduced,( th= is is happening out of Million one case)

<= div dir=3D"auto">This looks puzzle to us, just in case anyone experianced p= ls share your experience.

Regards,
Ravi

On Thu, 7 Nov, 2024, 3:41=E2=80= =AFam David Mullineux, <dmullx@gmail.com> wrote:
It might be worth elimin= ating the use of cached plans here. Is your app using prepared statements a= t all?=C2=A0=C2=A0
Point is that if the optimizer sees the= same prepared query , 5 times, the=C2=A0 it locks the plan that it found a= t that time. This is a good trade off as it avoids costly planning-time for= repetitive queries. But if you are manually querying, the=C2=A0 a custom p= lan will be generated=C2=A0 anew.
A quick analyze of= the table should reset the stats and invalidate any cached plans.
This may not be your problem=C2=A0 just worth eliminating it= from the list of potential causes.

On Wed, 6 Nov 2024, 17:14 Ramakris= hna m, <ram.pgdb@gmail.com> wrote:
Hi Team,

One of the queries, which retrieves a single record= from a table with 16 hash partitions, is taking more than 10 seconds to ex= ecute. In contrast, when we run the same query manually, it completes withi= n milliseconds. This issue is causing exhaustion of the application pools.= =C2=A0Do we have any bugs in postgrs16 hash partitions? Please find the att= ached log, table, and execution plan.=C2=A0

size of the each partitions : 300GB=C2=A0
Index Size : 12GB

Postgres Version : 16= .x
Shared Buffers : 75 GB
Effective= _cache :=C2=A0 175 GB
Work _mem : 4MB
Max_connections : 3000

OS=C2=A0 :=C2=A0Ubuntu 22= .04
Ram : 384 GB
CPU : 64

Please let us know if you need an= y further information or if there are additional details required.=C2=A0=C2= =A0


Regards,
Ram.
--0000000000005d80fc062672b5b6--