Robins: On Mon, Jul 6, 2020 at 1:37 PM Robins Tharakan <tharakan@xxxxxxxxx> wrote: > When an SQL needs to UNION constants on either side, it should be possible to > implicitly apply a LIMIT 1 and get good speed up. Is this an incorrect understanding, > or something already discussed but rejected for some reason? Maybe the optimization does not hold its weight. I mean, the increased complexity in the optimizer, bigger memory footprint, testing and developer usage, is not worth it. > This need came up while reviewing generated SQL, where the need was to return true when > at least one of two lists had a row. A simplified version is given below: I.e., I do not think this is a "need", specially in generated SQL, seems more like a deficiency in the generator ( specially since generators are able, and some do it, to massively overcondition the generated code to insure the optimizer does not miss anything ), and wrapping things in a limit 1 when just testing for row existence seems easy to do while generating. > (SELECT 1 FROM pg_class) UNION (SELECT 1 FROM pg_class); > vs. > (select 1 FROM pg_class limit 1) UNION (SELECT 1 FROM pg_class limit 1); -- Faster As an aside, isn't it easier, more correct ( in my opinion ) and clearer to just use exists for row-existence test? Because you have to at least see it there is a result above, probably using exists, and you can do... select exists(SELECT 1 FROM pg_class) or exists(SELECT 1 FROM pg_class); to get a direct boolean and benefit from shortcircuiting, by putting the most likely one first, and from the internal knowledge the optimizer may have to not fully evaluate queries, which may be greater than deducting from the union/limit case. Francisco Olarte.
