public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Robert Haas <[email protected]>
Cc: Andy Fan <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Dmitry Astapov <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not?
Date: Tue, 01 Mar 2022 21:05:02 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoZreVw+JomzAOz7h0XdPDJZLS7As648f9MjM3wMm8OCUw@mail.gmail.com>
References: <CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV+FcjyY6scooYA@mail.gmail.com>
<[email protected]>
<CAFQUnFiQJJctiZd_vO5Tt8yN=H9Pwpg4AfzcyRCCmEz6jnmAvQ@mail.gmail.com>
<[email protected]>
<CAApHDvpeaY_6+uudYp8qv42atqmPGA+mdfXWUM=oNnOmqBvEnw@mail.gmail.com>
<CAKU4AWpo9z0hMHDWUKuce4Z-NpcybV0J2UVu5+DVwyP-CrHCQg@mail.gmail.com>
<CA+TgmoaC9zGwxhoi6o0gAGXXBrkZxf+GRokNtaN9AmjjhD8j1g@mail.gmail.com>
<CAKU4AWppVGVLLV0GrwNMdYo=rdYUDka5RbeUdC528bSg4s5Dsw@mail.gmail.com>
<CA+TgmoZ89OpOCzXebNppQrU_wtQYimkHY3q3rWnS6ZmihA92+A@mail.gmail.com>
<CAKU4AWr_9rq_dqa=SaRL8QaL=z4LJAuL5wqtY4U82=ts14v7dw@mail.gmail.com>
<CA+TgmoZSJpZY78Etvyc-xrZ+acjGWDCyr7mjLQbQWB2U_gyHZg@mail.gmail.com>
<[email protected]>
<CA+TgmoZreVw+JomzAOz7h0XdPDJZLS7As648f9MjM3wMm8OCUw@mail.gmail.com>
Robert Haas <[email protected]> writes:
> I agree. My question is: why shouldn't every case where we can deduce
> an implied inequality be reasonably likely to show a benefit?
Maybe it will be, if we can deal with the issue you already mentioned
about not misestimating the resulting partially-redundant conditions.
> Second, it looks to me like the patch takes the rather naive strategy
> of enforcing the derived clauses everywhere that they can legally be
> put, which seems certain not to be optimal.
I'm not sure about that ... it's basically what we do with derived
equalities. However, there's enough structure in the equivalence-class
case that we don't end up enforcing redundant quals. It's not clear
to me whether the same can be said here.
> I don't know whether attaching something to the equivalence class data
> structure is the right idea or not. Presumably, we don't want to make
> an extra pass over the query tree to gather the information needed for
> this kind of optimization, and it feels like we need to know which
> vars are EMs before we try to derive alternate/additional quals.
Yeah, we don't want to make an additional pass over the tree, and
we also would rather not add an additional set of per-operator
catalog lookups. We might be able to generalize the code that looks
for equality operators so that it looks for "any btree operator"
with the same number of lookups, and then have it feed the results
down either the EquivalenceClass path or the inequality path
as appropriate. At the end, after we've formed all the ECs, we
could have a go at matching up the inequality structures with the
ECs. But I don't agree that ECs are a necessary prerequisite.
Here are a couple of other patterns that might be worth looking for:
* "a > b AND b > c" allows deducing "a > c", whether or not any
of those values appears in an EC.
* "a > const1 AND a > const2" can be simplified to either "a > const1"
or "a > const2" depending on which constant is larger. (The predicate
proof mechanism already has a form of this, but we don't typically
apply it in a way that would result in dropping the redundant qual.)
It's entirely possible that one or both of these patterns is not
worth looking for. But I would say that it's equally unproven
that deriving "a > c" from "a = b AND b > c" is worth the cycles.
I'll grant that it's most likely going to be a win if we can use
any of these patterns to generate a restriction clause from what
had been join clauses. Beyond that it's much less clear.
regards, tom lane
view thread (15+ messages) latest in thread
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], [email protected]
Subject: Re: Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not?
In-Reply-To: <[email protected]>
* 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