public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: James Brown <[email protected]>
Cc: [email protected]
Cc: Gabriel Sinkin <[email protected]>
Subject: Re: optimizing a join against a windowed function
Date: Sat, 31 Aug 2024 01:27:44 +1200
Message-ID: <CAApHDvpvaf7M=Fz0vHW3TQMX3oco7L11O5Sa3TO3uUF8pHvQnw@mail.gmail.com> (raw)
In-Reply-To: <CA+7GS+Ff3PWbeJNbi7e6Nqfgh4HOTzyNoyGsBwFeivkUKDUwBA@mail.gmail.com>
References: <CA+7GS+Ff3PWbeJNbi7e6Nqfgh4HOTzyNoyGsBwFeivkUKDUwBA@mail.gmail.com>

On Fri, 30 Aug 2024 at 23:36, James Brown <[email protected]> wrote:
> I have two tables: one named taxpayers which has a goodish number of columns an an integer PK id, and one named insights, which has a taxpayer_id foreign key to taxpayers, a year, and (again) a lot of other columns. There's an index on insights (taxpayer_id, year DESC). I'm executing the following SQL:

> If there's only a single value in the IN clause, the EXPLAIN plan looks great:

> However, if there are multiple rows in the IN clause, the optimizer decides to execute the subselect against the entire giant table, and it is not great:

Unfortunately, you've hit a limitation with the EquivalenceClass code.
With the "ON latest_insights.taxpayer_id = taxpayers.id WHERE
taxpayers.id = 650974", the planner is able to deduce that
latest_insights.taxpayer_id is also equal to 650974 and push that
condition down into the common table expression. With the "ON
latest_insights.taxpayer_id = taxpayers.id WHERE taxpayers.id IN (?,
?)" query, the EquivalenceClass code doesn't handle this, so the
optimisation isn't performed. We likely should improve this someday,
but for today, think of it as an unimplemented optimisation rather
than a bug.

> If I add in a second repetitive WHERE clause, it goes back to being happy, but that feels a bit like a hack:

That's likely your best bet on how to make the planner do what you
want, provided you're able to given the query is inside a view.

David

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: optimizing a join against a windowed function
  In-Reply-To: <CAApHDvpvaf7M=Fz0vHW3TQMX3oco7L11O5Sa3TO3uUF8pHvQnw@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox