Re: Apply LIMIT when computation is logically irrelevant - Mailing list pgsql-general

From Simon Riggs
Subject Re: Apply LIMIT when computation is logically irrelevant
Date
Msg-id CANP8+jJ1Jy7s5krytzQHO2n8BF0CtUA0GZtyhTSfMV7GOA-x_w@mail.gmail.com
Whole thread Raw
In response to Re: Apply LIMIT when computation is logically irrelevant  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: Apply LIMIT when computation is logically irrelevant
List pgsql-general
On Mon, 6 Jul 2020 at 21:49, David Rowley <dgrowleyml@gmail.com> wrote:
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?

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

pgsql-general by date:

Previous
From: David Rowley
Date:
Subject: Re: Apply LIMIT when computation is logically irrelevant
Next
From: David Rowley
Date:
Subject: Re: Apply LIMIT when computation is logically irrelevant