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 1t9iQm-00BqBf-Oz for pgsql-general@arkaria.postgresql.org; Sat, 09 Nov 2024 10:11:00 +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 1t9iQi-000Acx-94 for pgsql-general@arkaria.postgresql.org; Sat, 09 Nov 2024 10:10:56 +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 1t9iQh-000Aco-QX for pgsql-general@lists.postgresql.org; Sat, 09 Nov 2024 10:10:56 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t9iQd-0010NH-Ul for pgsql-general@lists.postgresql.org; Sat, 09 Nov 2024 10:10:55 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-2884a6b897cso1602052fac.3 for ; Sat, 09 Nov 2024 02:10:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731147050; x=1731751850; 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=2mkcMAIwwYPHaDcKVEsxV4R9zBgkmRFYl9XMfkmD6OI=; b=Cx5Z8JeRd+N/wWv+WsMunPDC5qC6fk+qDi7TWK4dCM41o9fx60cFMMFVAfDl2WsY6i fTQA/Njdbe+wsOxe9PcgvZ7s4mOzOgqqNAvUEVUjQalpXWwzJHkfrtuUV0uZ5YFxOcO8 Ei4gy0hHxQ/oKfsYO4vyB9lIFixm+13iNy/7abkIcGMGgiUqZlM+yuaUEnKcwmjM6dkn a7lPlkFeYf7jpBDl/USuDdUWhxMhIEOeOpRRug0yBleVOOMdnM5eQbTa0+eKRW7IKyr/ IpbysQCZTaIC8zDxTg3weivaewKnu1p2rnGgT7nboZKraiH2KkvjDQPTm3ViDi3IjyYE 1DZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731147050; x=1731751850; 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=2mkcMAIwwYPHaDcKVEsxV4R9zBgkmRFYl9XMfkmD6OI=; b=GFS1jm7spSE/WvDtx72Jn2phEeVzlAnQsj491hCSEw11h4Wf4Y0wo1UlJfNtwHPrn9 jydqXsDSTMzs8W2ydHy+uAs1UcrxBz7cHMBRV1zXxqvU8dDs+j7b4WLY3ZecrHkeIYA7 DUIRW28dxXJxRwn9qY2r6BYH+So7jzGLslyc+JEzH2Xzsa7DXehA6zyxSVr3kwSUFOIH bOjbEYkgJGx1Fqw+nff3Ui6TT48MCp0EYIf9QV2X1zNQGbohgUZJ6DZCl1SyuWVOxjUp xU/Ns+zBxC8mGSFLP+Oc4EZGSHRHsldgKjitjtsgi1qvm4U1zxOh++tGiUgLlrDrZX+h pxiQ== X-Forwarded-Encrypted: i=1; AJvYcCUJeEI0LLQMDgsJzCzX2ITCUgcVnW/7tSbsmenUpF2c9a+J6uP6RsEmynv7CNGGzYI96yHJxBSGbJQhKHeE@lists.postgresql.org X-Gm-Message-State: AOJu0YwjeVPD7oWxJ565i7RYlbX9szw8WTaVfaZSwJxHLPOUA6Jt35gw mnIlXXuQ/puEX+I0oQDdp2lnSvJ+DdB5+2KkM1vT02kbJ5/dsDhb4wZl5d2+veMkHG+ekx4JNak gcYpFXyZWFTKwtlwrf8Bf1hPMERg= X-Google-Smtp-Source: AGHT+IGiZvBTvSIqo2NRR1cS1GrZ5yNsD9jj4a4dd2PlyNMbYcVZVv8UVAVWNabXEK/KmESp+CDNet8rn52b6RMAy1M= X-Received: by 2002:a05:6870:bacb:b0:27c:4f04:a1f1 with SMTP id 586e51a60fabf-295602608b9mr5997933fac.32.1731147050487; Sat, 09 Nov 2024 02:10:50 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: David Mullineux Date: Sat, 9 Nov 2024 10:10:39 +0000 Message-ID: Subject: Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16 To: ravi k Cc: Ramakrishna m , pgsql-general Content-Type: multipart/alternative; boundary="000000000000ff95540626781425" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ff95540626781425 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for correction. At this point I would be trying to modify plan_cache_mode for the session which uses the bond variable. alter it so that plan_cache_mode=3Dforce_custom_plan One hypothesis is that, a bad plan got cached for that SQL pattern. Obviously, when you run it *manually* you are always getting a *custom* plan as it's not a prepared statement. On Sat, 9 Nov 2024, 03:46 ravi k, wrote: > Sorry, it was typo. Bind variable is bigint only. > > Thanks > > On Fri, 8 Nov, 2024, 7:09=E2=80=AFpm David Mullineux, = wrote: > >> 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 = of >>> 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 >>> 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 face latency issues and lower environment this issue ca= nnot >>> 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 ap= p >>>> 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 trad= e off >>>> as it avoids costly planning-time for repetitive queries. But if you a= re >>>> 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 >>>> of 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 con= trast, >>>>> when we run the same query manually, it completes within milliseconds= . This >>>>> issue is causing exhaustion of the application pools. Do we have any = bugs >>>>> in postgrs16 hash partitions? Please find the attached log, table, an= d >>>>> 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 ar= e >>>>> additional details required. >>>>> >>>>> >>>>> Regards, >>>>> Ram. >>>>> >>>> --000000000000ff95540626781425 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for correction. At this point I would be trying to= modify=C2=A0
plan_cache_mode
for t= he session which uses the bond variable. alter it so that plan_cache_mode= =3Dforce= _custom_plan
One hypothesis is that, a bad plan got cached for that SQL p= attern. Obviously, when you run it manually you are always getting a= custom plan as it's not a prepared statement.=C2=A0=C2=A0




On Sat, 9 Nov 2024, 03:46 ra= vi k, <ravisql09@gmail.com>= ; wrote:
Sorry, i= t was typo. Bind variable is bigint only.

Thanks=C2=A0

On Fri, 8 Nov, 2024, 7:09=E2=80=AFpm David Mu= llineux, <dmullx@gmail.com> wrote:
Just spotted a potential problem. The indexed col= umn 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 con= version when you run it manually.
Sometimes the wron= g type will make it not use the index.

On Fri, 8 Nov 2024, 03:07 ravi = k, <ravisql09@gmail.com> 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 possib= ility 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 partitions 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 face latency issues and lower environment this issue cannot= be reproduced,( this is happening out of Million one case)

This looks puzzle to us, just in case= anyone experianced pls share your experience.

<= /div>
Regards,
Ravi

=
On Thu, 7 = Nov, 2024, 3:41=E2=80=AFam David Mullineux, <dmullx@= gmail.com> wrote:
It might be worth eliminating the use of cached plans here. Is yo= ur app using prepared statements at 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 at that time. This is a good trade off as = it avoids costly planning-time for repetitive queries. But if you are manua= lly querying, the=C2=A0 a custom plan will be generated=C2=A0 anew.
A quick analyze of the table should reset the stats and inv= alidate any cached plans.
This may not be your probl= em=C2=A0 just worth eliminating it from the list of potential causes.
=

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

One of= the queries, which retrieves a single record from a table with 16 hash par= titions, is taking more than 10 seconds to execute. In contrast, when we ru= n the same query manually, it completes within milliseconds. This issue is = causing exhaustion of the application pools.=C2=A0Do we have any bugs in po= stgrs16 hash partitions? Please find the attached log, table, and execution= plan.=C2=A0

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

Postgres Version : 16.x
Shared B= uffers : 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 any further information or if ther= e are additional details required.=C2=A0=C2=A0


Regards,
Ram= .
--000000000000ff95540626781425--