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 1nAsF5-0006nS-Gp for pgsql-performance@arkaria.postgresql.org; Fri, 21 Jan 2022 11:38:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nAsF3-0007cJ-8n for pgsql-performance@arkaria.postgresql.org; Fri, 21 Jan 2022 11:38:05 +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 1nAsF2-0007cA-Ss for pgsql-performance@lists.postgresql.org; Fri, 21 Jan 2022 11:38:04 +0000 Received: from mail-ed1-x536.google.com ([2a00:1450:4864:20::536]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nAsEw-0001eQ-7P for pgsql-performance@postgresql.org; Fri, 21 Jan 2022 11:38:03 +0000 Received: by mail-ed1-x536.google.com with SMTP id c24so39587284edy.4 for ; Fri, 21 Jan 2022 03:37:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:thread-index:content-language; bh=Eb+yDlI77DvKs4wDlRYWdRVbqB99rsgGHGvXjO7Ocm4=; b=gzjGz3qmO88WfrWdADOJNWGb76z4fdabsqa4WuilX0DXdqva/dYy5BGF4KD/bOdxjK T/Gy2kdOC2rsDtJ2wKXLqpNps67DOd87ZsO0x+kWgYA5e1puNgnqDMOBBc/nn9KH1y/Q 1l1H4grKHQ2haXqUYWhXOpeSsSPABP7UcVuPlF4MCB9GJq30gcc6nQk+ByJukmQBIL7n 1TLmzXRFnYM4J02M9OX2VqTVx97BC1jPcepRPT6ctAdrvDP3CEH4zL2osguXLizs3Dik ZL2MUMTmsYlgH2LK/wtuhrL2hWeogVYClrvXvS9P/X9OriyJYP903lH0r/0ddOyHlLF4 N7CA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=Eb+yDlI77DvKs4wDlRYWdRVbqB99rsgGHGvXjO7Ocm4=; b=UOhNJh1wRlV6LzhUJpICo5FN1mQHSCjt+lI7lZMN9QHBnf3m2G9+xzcmTtOTcaUl+B VytykFRPBrxN6+CZS+tyz2FjpJQpP0gKmhbdEmX/ChdeX9KkarIrQP6oDTNlspFQ+eQO zcVpjhGAWsh8E2noQEYYbo9s6fobDDhH9lbtcejkwuX1avNoW7/Wh7hJXE/EtccdhtcC +mRMHo8m6tcNYAKJG8zzysgMDWTYQUdUSfnnnwK2jrjXS0p0LbpTyuJaN6O4JNvFs8YE FjjcFZpCwvWGxKpPV/L8Mq+LDL6Z3FSF2jIkogAANvVo1Xo/KAFaCH299lqcb5XSdB82 G/5A== X-Gm-Message-State: AOAM533gJCpsqkxzSOK+TI2Oxi2+9S+CJH9pee7KWTp/OhxN5ADEJ9Cl PAmJgeof1Z8S67xPOL0ApfqdQrr3/uA= X-Google-Smtp-Source: ABdhPJywuUfVGBvfjFK/J37vvk7euJhVEZ0uFL4XYauEfMIMxbJfEXnFkba2Nxr3iziXUs0/KdQ1/Q== X-Received: by 2002:a17:906:43d3:: with SMTP id j19mr2936882ejn.568.1642765076752; Fri, 21 Jan 2022 03:37:56 -0800 (PST) Received: from EMEA28197 ([92.62.10.204]) by smtp.gmail.com with ESMTPSA id g17sm280518ejd.82.2022.01.21.03.37.56 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 21 Jan 2022 03:37:56 -0800 (PST) From: To: "'Michael Lewis'" Cc: References: In-Reply-To: Subject: RE: Slow query fixed by replacing equality with a nested query Date: Fri, 21 Jan 2022 11:37:54 -0000 Message-ID: <003801d80ebb$54ac97a0$fe05c6e0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0039_01D80EBB.54ACE5C0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQFYxWz+DZ4ZCbxLIMjQr2iD2uf1pgFRHUo2rWFbX4A= Content-Language: en-gb List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0039_01D80EBB.54ACE5C0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable My mistake. I have updated the query in the gist: cte1 should have been = referenced in cte2.=20 The query plans are correct. It was just the query in the gist that was = incorrect (I was just verifying cte1 was the culprit =E2=80=93 without = it the query is fast too). =20 This SQL query is a result of translating a FHIR query into SQL. These = queries are generated on the fly from user input. The chains will not = always be linear. But I guess I could write an optimizer that rewrites = linear parts as JOINS. If that would result in better query plans. =20 I have done a few simple experiments in the past comparing CTEs like = this to JOINS, but the resultant query plans were the same. CTEs seemed = easier to follow when troubleshooting issues, so I left them as such. Do = JOINs become better than CTEs at a certain point? =20 I will attempt to rewrite the query with JOINs on Monday to see if it = makes a difference. It might be tricky, the relationship from resource = table to search parameter tables is often a 1 to many. =20 ------=_NextPart_000_0039_01D80EBB.54ACE5C0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

My mistake. I have updated the = query in the gist: cte1 should have been referenced in cte2. =

The query plans are correct. It was = just the query in the gist that was incorrect (I was just verifying cte1 = was the culprit =E2=80=93 without it the query is fast = too).

 

This SQL = query is a result of translating a FHIR query into SQL. These queries = are generated on the fly from user input. The chains will not always be = linear. But I guess I could write an optimizer that rewrites linear = parts as JOINS. If that would result in better query = plans.

 

I have done = a few simple experiments in the past comparing CTEs like this to JOINS, = but the resultant query plans were the same. CTEs seemed easier to = follow when troubleshooting issues, so I left them as such. Do JOINs = become better than CTEs at a certain point?

 

I will = attempt to rewrite the query with JOINs on Monday to see if it makes a = difference. It might be tricky, the relationship from resource table to = search parameter tables is often a 1 to many.

 

------=_NextPart_000_0039_01D80EBB.54ACE5C0--