Re: Apply LIMIT when computation is logically irrelevant

[Simon Riggs <simon@xxxxxxxxxxxxxxx>]

On Mon, 6 Jul 2020 at 12:37, 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?

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/
Mission Critical Databases