From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Robins Tharakan <tharakan(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Apply LIMIT when computation is logically irrelevant |
Date: | 2020-07-06 20:49:18 |
Message-ID: | CAApHDvrzOyNXvu_Yfu2fhhhGC3-3_GJ+0vcrQ-aQiF=UpWuKRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
David
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2020-07-06 21:03:27 | Re: Apply LIMIT when computation is logically irrelevant |
Previous Message | Sebastien Arod | 2020-07-06 20:28:48 | Re: Is postgres able to share sorts required by common partition window functions? |