On Mon, Nov 25, 2024 at 4:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm a little skeptical that we should expend a lot more effort on
> the sorts of cases discussed here. Basically, this sort of patch
> improves matters for people who write crummy queries while penalizing
> everybody else.
I think that it's more complicated than that. Rather than explain what
I mean in general terms, I'll give you a specific example of the kind
of thing that seems quite interesting to me:
It would be fairly easy to teach nbtree about a new kind of
ScalarArrayOp that worked just like a conventional SAOP, but also
returned tuples matching "IS NULL" (IS NULL uses the equals strategy
internally already, so it'd be almost the same as treating NULL as
just another array element). This could have significant advantages
over what's even possible right now, particularly in cases involving
ORDER BY ... LIMIT.
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"? Theoretically
this would still be a case that amounted to improving matters for
badly written queries...but not really (we can hardly expect many
users to adopt our esoteric non-standard syntax).
In fact, you could make a similar argument for rewriting IN() into a
"= ANY()" SOAP (in the way that we always have).
> We need to be very careful about adding cycles to
> planner runtime in pursuit of optimizations that are only rarely
> successful.
I agree.
--
Peter Geoghegan
