Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qDmqZ-0004Xr-Vr for pgsql-general@arkaria.postgresql.org; Mon, 26 Jun 2023 14:05:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qDmqY-0002G0-LZ for pgsql-general@arkaria.postgresql.org; Mon, 26 Jun 2023 14:05:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qDmqY-0002FX-66 for pgsql-general@lists.postgresql.org; Mon, 26 Jun 2023 14:05:38 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qDmqU-000YbT-Q2 for pgsql-general@lists.postgresql.org; Mon, 26 Jun 2023 14:05:36 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-570808d8ddeso37523027b3.0 for ; Mon, 26 Jun 2023 07:05:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687788334; x=1690380334; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ERzrRHVjrgSboG+Bi0HE9Nlpv+0JBVHbZL+9wpf/c8Y=; b=K05b7dq3WnSEBzfyD3DKdMPq1jKDs4rhDRBRrtxCvBDlsc30sW1xxG5KmwijRAzL0M SWIApkoGS83KtYOdzaUWhO/WRCpPT+zG+LzMt54FpaGrH3R/3tRL1iwhKaXSASA+GJoe SC96qvT+WriafDnkdDOyNMKdsJqGaZfcdMVhqw/NqIK+3eSfiaUu8/wbDu92cYH34bDN rf5N+xnSLjnuZ2VzWYb2lSzXvGWV4wa6l6R5wA2xmQF0RYBIxAEZCVptOnk9MCnSSLaz 4QxuYtgYvmU6H1G4xOsfW9dK2c9RBjYSLjm4XINcHL7Mg1GcVIcMTCvbJiluwCL7zgv2 cF4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687788334; x=1690380334; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ERzrRHVjrgSboG+Bi0HE9Nlpv+0JBVHbZL+9wpf/c8Y=; b=KsCnWx3RJ2TLm1i2CaBEKOQQ+Lfz5uGmRUpNrUxZrPnrWosDCgxgak8gx0PtWtA11L 7gSdaLjmwM2oz3TrwMgveuBpS3c3v7qyg4GSp2WKztEXOdrCAjoMwwGjvj3k/SlZO18R EcgzSyl1PJAsPJeFJ0Aslm5v13uFM/LyKEs1bCbljn85uonnqd0rDlhQNVnIchGLah/I b8bjffSnfF1OBAdJ1IstPwAwA1Byyb/0k/a2X7goex9AIKuU2TTc7SPmrhXjVYdSl4// jmpo2ChwoG/Yphp1fsCWlF0/TlzHE5k/FLfNhIHdun/bXfxw9phmQ5HkUmephXw24onv lyuA== X-Gm-Message-State: AC+VfDxPknf3dEMScxQlgkB0hqRg54Av+D7A0r4sop3siv7YMAMlNuvd LJsHEcODA5RZ+E9AoC1N9sEJGycUbMQ= X-Google-Smtp-Source: ACHHUZ6P7Nb+TfqlytStbG2LqSOKsXqx4tWjEwBzMPVwkvrZKdenjtRwk0RrG2ED7mDW3h5mmye8bQ== X-Received: by 2002:a0d:ea91:0:b0:56c:fce5:ac38 with SMTP id t139-20020a0dea91000000b0056cfce5ac38mr30212575ywe.14.1687788333615; Mon, 26 Jun 2023 07:05:33 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:f8c6:8913:77fd:2653? ([2600:1700:bd2c:b810:f8c6:8913:77fd:2653]) by smtp.googlemail.com with ESMTPSA id x191-20020a81a0c8000000b00565de196516sm1307959ywg.32.2023.06.26.07.05.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 26 Jun 2023 07:05:33 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------E0zX0f0RzHmfGKe7ApviqWQc" Message-ID: <77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.com> Date: Mon, 26 Jun 2023 09:05:31 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: bug or lacking doc hint Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <587381.1687729675@sss.pgh.pa.us> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------E0zX0f0RzHmfGKe7ApviqWQc Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 6/26/23 07:22, Marc Millas wrote: > > > On Mon, Jun 26, 2023 at 5:47 AM Avin Kavish wrote: > > Sounds like the problem you are having is, the server is running out > of temporary resources for the operation that users are trying to do. > So according to Tom, on the postgres side, the operation cannot be > optimized further. > > I think you have few choices here, >   - See if increasing the resources of the server will allow them to > run the operation >   - Ask users not to do that operation >   - Use a extension like citus to scale horizontally > > But I'm thinking why a massively inefficient join is needed in the > first place. Shouldn't joins be for following keyed relationships. So > ideally a unique indexed column, but at the very least an indexed > column. Why is a join required on a dynamically calculated substring? > Can it be made into a static computed value and indexed? Substring > sounds like an op that should be in the filter stage. > > Can you describe your data model? Maybe we can give some specific advice. > > > There is a set of big tables containing fine grain health data. The DB is > accessed by people doing research on various kind of sicknesses. So, by > nature, all columns (I mean ALL) can be used for every kind of SQL > including, obviously, lots of intricated joins. This sounds like the kind of problem normally solved by data warehouses.  Is your schema designed like a DW, or is it in 3NF? -- Born in Arizona, moved to Babylonia. --------------E0zX0f0RzHmfGKe7ApviqWQc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 6/26/23 07:22, Marc Millas wrote:


On Mon, Jun 26, 2023 at 5:47 AM Avin Kavish <avin@baseboard.ai> wrote:
Sounds like the problem you are having is, the server is running out of temporary resources for the operation that users are trying to do. So according to Tom, on the postgres side, the operation cannot be optimized further.

I think you have few choices here,
  - See if increasing the resources of the server will allow them to run the operation
  - Ask users not to do that operation
  - Use a extension like citus to scale horizontally

But I'm thinking why a massively inefficient join is needed in the first place. Shouldn't joins be for following keyed relationships. So ideally a unique indexed column, but at the very least an indexed column. Why is a join required on a dynamically calculated substring? Can it be made into a static computed value and indexed? Substring sounds like an op that should be in the filter stage.

Can you describe your data model? Maybe we can give some specific advice.

There is a set of big tables containing fine grain health data. The DB is accessed by people doing research on various kind of sicknesses. So, by nature, all columns (I mean ALL) can be used for every kind of SQL including, obviously, lots of intricated joins.

This sounds like the kind of problem normally solved by data warehouses.  Is your schema designed like a DW, or is it in 3NF?

--
Born in Arizona, moved to Babylonia.
--------------E0zX0f0RzHmfGKe7ApviqWQc--