On Mon, Nov 25, 2024 at 4:39 PM Tom Lane <t...@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
