Re: Apply LIMIT when computation is logically irrelevant

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.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
Mission Critical Databases