From: val.janeiko@gmail.com
To: 'Michael Lewis' <mlewis@entrata.com>
Cc: pgsql-performance@postgresql.org
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> (raw)
In-Reply-To: <CAHOFxGoOz4RYH2Zj9C6EqcqLKKTwv=76onrOK0_DJGC8Fhu8vQ@mail.gmail.com>
References: <CAPhGPA5OYUN8m6R=Z429BVBYTpYMvXqhSG3ZbZECpcEyjaDWvQ@mail.gmail.com>
<CAHOFxGoOz4RYH2Zj9C6EqcqLKKTwv=76onrOK0_DJGC8Fhu8vQ@mail.gmail.com>
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 – 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: val.janeiko@gmail.com, mlewis@entrata.com
Subject: RE: Slow query fixed by replacing equality with a nested query
In-Reply-To: <003801d80ebb$54ac97a0$fe05c6e0$@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox