On Mon, Nov 25, 2024 at 6:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Geoghegan <pg@bowt.ie> writes:
> > I suppose that we'd have to invent some kind of new syntax for this.
> > But wouldn't it also make sense if it worked with "WHERE a IN (1, 2)
> > OR a IS NULL"? Or even with "WHERE a = 1 OR a IS NULL"?
>
> I'd be a strong -1 for inventing new syntax for that. However, if
> that's actually a common query pattern (I'm not convinced of it)
> we could certainly build something to recognize that usage and
> transform it into a suitable executable structure.
My basic point is this: SQL is supposed to be declarative -- in theory
it isn't supposed to matter how you write the query.
I don't see any hard distinction between the sorts of transformations
that the OP is talking about, and what you're describing here. There's
quite a lot of gray area, at least.
> However, I don't see the connection between that and the current
> thread. That pattern is not self-contradictory. My doubts
> about its usefulness have more to do with it being evidence of
> the SQL anti-pattern of treating NULL as a normal data value.
It's just an example, chosen to be easy to explain. I guess you didn't
like that example, so I'll go with another:
What about the "general OR optimization" stuff described by the MDAM
paper? The redundant and contradictory qual stuff is needed there, to
make sure that the final index scan access path (consisting of a
series of disjunctive index scans in key space order) will reliably
avoid returning duplicate rows.
(FWIW I think that the OP took some cues from the MDAM paper, since
they talked about doing things like transforming SQL Standard row
value constructor expressions into disjuncts.)
I don't have a concrete proposal here, or anything like it. I just
want to express that I believe that there's a lack of joined-up
thinking about nbtree preprocessing and the optimizer (not for the
first time). We *are* missing a few interesting tricks here.
--
Peter Geoghegan
