On Mon, 6 Jul 2020 at 21:49, David Rowley <dgrowle...@gmail.com> wrote:
> On Tue, 7 Jul 2020 at 00:43, Simon Riggs <si...@2ndquadrant.com> wrote: > > > > On Mon, 6 Jul 2020 at 12:37, Robins Tharakan <thara...@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? -- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> Mission Critical Databases
