Postgres Pro
Downloads
Home   >   mailing lists

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+jL+37nWsPUn1v-FZc2htPhRVncG4W=Q19i2YVWP_jkz1Q@mail.gmail.com
Whole thread Raw
In response to Apply LIMIT when computation is logically irrelevant  (Robins Tharakan <tharakan@gmail.com>)
Responses Re: Apply LIMIT when computation is logically irrelevant
List pgsql-general
Tree view
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.

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

pgsql-general by date:

Previous
From: Matthias Apitz
Date:
Subject: Re: PostgreSQL server does not increment a SERIAL internally
Next
From: Tom Lane
Date:
Subject: Re: SV: Using Postgres jdbc driver with Oracle SQL Developer