Re: Apply LIMIT when computation is logically irrelevant

On Mon, 6 Jul 2020 at 21:49, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 7 Jul 2020 at 00:43, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >
> > On Mon, 6 Jul 2020 at 12:37, Robins Tharakan <tharakan(at)gmail(dot)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