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 1t9FLa-008evm-P1 for pgsql-general@arkaria.postgresql.org; Fri, 08 Nov 2024 03:07:42 +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 1t9FLX-005P5a-0m for pgsql-general@arkaria.postgresql.org; Fri, 08 Nov 2024 03:07:39 +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 1t9FLW-005P5S-Hl for pgsql-general@lists.postgresql.org; Fri, 08 Nov 2024 03:07:39 +0000 Received: from mail-lf1-x12a.google.com ([2a00:1450:4864:20::12a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t9FLU-000jW7-As for pgsql-general@lists.postgresql.org; Fri, 08 Nov 2024 03:07:37 +0000 Received: by mail-lf1-x12a.google.com with SMTP id 2adb3069b0e04-539e4b7409fso1792612e87.0 for ; Thu, 07 Nov 2024 19:07:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731035254; x=1731640054; 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=xHBHPehQC8gwQLBoY0/EVWnlvjkJz3EGjHR/AWs/fb8=; b=JU6V5wuS7k1gQbE8koiy+lEfNkoC2QvQjawT95ScDQ7DIxiW84T2c0porlgIF+PJaG oz0E5jxe0c2goIg0WZy/RqwEPn0SrXYIEs3tNLXTihiwlomaRv54Fv9CQLuE2EtEYIyd sJLpImZRpFTe85oMsWdV95dMQUtcoe+3a19pkxUn2lz+51WjOKWkQLsqBWp9l02lQxLR d5ucAcU3c0EyH2KCdOSNiWrGJHX67QTIWeghU+ydyauXiFCjzoYG4oCb/vPIxdD87ZP7 Ab9iYgXmxuJQrZBPRXAeetxznEbY5Z39+Pozqt3VRfelPB6MK/6m0iszGAoAOW20Qxl2 vySw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731035254; x=1731640054; 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=xHBHPehQC8gwQLBoY0/EVWnlvjkJz3EGjHR/AWs/fb8=; b=PAyud9Og3+bhZ5Nai+OIzWiSRjRqGJ0DDxnvmSSWf5FRw9GY4txwPqV581SzF52Chk paDfBU+I5w70hXj1wnUpDhe/+VAI0U4qUEvvqHzKMDhzULn4Nq4ZSJgA8LiIC8bnWIV2 mhPR1l+kbYyvZzbRZA+TU9exFc2aZZeKZ7e6HfmYWOdARImXvSwvk5Xb4bbE8bcPXcak aT+5ISO6+ymdJiBvQYPqAYjNJ8ZHZl3Mn21wNXzLHV+TMefylbxZdx2i67k0RzCMwd7D 1GUCZVhveMNhRzk/YErFk6rHOvn/TRGXfHEJtc3jyhMa1yqcnOijp3/zptfp/n/l5MkA ZFNg== X-Forwarded-Encrypted: i=1; AJvYcCXVVXrLs1kcfk8QqDNFNklrkWLn0q/Mai+6gNvRiOTK/7oZnqcC2JOQk7MzWrBKORFbThzk8iFEFRnFmJPQ@lists.postgresql.org X-Gm-Message-State: AOJu0YzkLXSm/umJH20csixrpV3Fn24bqdUG3bGJpe+LpJS4stcCYcpG oV9/kvZoqQZj+9hgUs4tz3ZjkOtNcUITGzKYalkPOUuepEGVxNMeFqAnwLEyTEdV2Ude+YmJnON qABSxeAj34YkcJ2EYbMoPBp3RtgI= X-Google-Smtp-Source: AGHT+IHfohjU0rgmScuUwPy8jur1iB8XIm12O0qtaerf0NZTw1qY0LaKEKQZFKguhF97U3BEx6QNsWkmkH55oyBTTpc= X-Received: by 2002:a05:6512:4009:b0:539:918c:5132 with SMTP id 2adb3069b0e04-53d85e8a959mr586590e87.0.1731035253754; Thu, 07 Nov 2024 19:07:33 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: ravi k Date: Fri, 8 Nov 2024 08:37:21 +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="00000000000064d1bb06265e0d00" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000064d1bb06265e0d00 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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. Regards, Ravi On Thu, 7 Nov, 2024, 3:41=E2=80=AFam David Mullineux, wr= ote: > 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 o= ff > as it avoids costly planning-time for repetitive queries. But if you are > 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 contrast, >> when we run the same query manually, it completes within milliseconds. T= his >> issue is causing exhaustion of the application pools. Do we have any bug= s >> 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. >> > --00000000000064d1bb06265e0d00 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi ,

Thanks = for the suggestions.

Two= more observations:

1) n= o sequence 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 par= titions also it could have been completed in milliseconds as this is just o= ne record.

4) auto_expla= in in prod we cannot enable as this is expensive and with high TPS we may f= ace 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.

Regards,
Ravi

On Thu, 7 Nov, 2024, 3:41=E2= =80=AFam David Mullineux, <dmullx@gm= ail.com> wrote:
It might be worth eliminating the use of cached plans here. Is your a= pp 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 a= voids costly planning-time for repetitive queries. But if you are manually = querying, the=C2=A0 a custom plan will be generated=C2=A0 anew.
A quick analyze of the table should reset the stats and invalid= ate 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 Ramakrishna m, <ram.pgdb@gmail.com> wrote= :
Hi Team,

One of the queries, which retrieves a sin= gle record from a table with 16 hash partitions, is taking more than 10 sec= onds to execute. In contrast, when we run the same query manually, it compl= etes within milliseconds. This issue is causing exhaustion of the applicati= on pools.=C2=A0Do we have any bugs in postgrs16 hash partitions? Please fin= d the attached log, table, and execution plan.=C2=A0

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

Postgres Ver= sion : 16.x
Shared Buffers : 75 GB
Effective_cache :=C2=A0 175 GB<= br>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 there are additional details requir= ed.=C2=A0=C2=A0


Regards,
Ram.
--00000000000064d1bb06265e0d00--