On Tue, 7 Jul 2020 at 00:43, Simon Riggs <simon@2ndquadrant.com> wrote: > > On Mon, 6 Jul 2020 at 12:37, Robins Tharakan <tharakan@gmail.com> 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? >> >> 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: >> >> (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 > > > Those two queries aren't logically equivalent, so you can't apply the LIMIT 1 as an optimization. > > First query returns lots of random rows, the second query returns just one random row.
I think the idea here is that because the target list contains only constants that pulling additional rows from the query after the first one will just be a duplicate row and never add any rows after the UNION is processed.
OK, I see. Are you saying you think it's a worthwhile optimization to autodetect?
