Subject: Re: \if, \elseif, \else, \endif (was Re: PSQL commands:
 \quit_if, \quit_unless)
To: Fabien COELHO <coelho@cri.ensmp.fr>, Corey Huinker
	<corey.huinker@gmail.com>
References: <fe2e8a9c-5a08-44d6-9767-f4dabd7c4f92@manitou-mail.org>
 <alpine.DEB.2.20.1701262143090.26793@lancre>
 <CADkLM=cr2z935TF3eB9jByWiijv-q2B7--QggfUUB12gCFFByQ@mail.gmail.com>
 <CADkLM=di=8WQRQDku6mfN=-pBqVQq_3osAARtVb6Tf0U8vyb7A@mail.gmail.com>
 <alpine.DEB.2.20.1701280721020.13068@lancre>
 <CADkLM=dm+d_FgtK4wqVkKr-X+naHXw60x+0veb04idFxYB141g@mail.gmail.com>
 <alpine.DEB.2.20.1701290916420.13068@lancre>
CC: Daniel Verite <daniel@manitou-mail.org>, PostgreSQL
	<pgsql-hackers@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <b0d46850-bf15-882c-a0ad-352e8631efe4@BlueTreble.com>
Date: Sun, 29 Jan 2017 15:49:18 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: <alpine.DEB.2.20.1701290916420.13068@lancre>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:99
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 30 Jan 2017 03:39:44.0357 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1640
X-Pg-Spam-Score: -1.5 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 1/29/17 2:35 AM, Fabien COELHO wrote:
>>> I'm wondering what pg would do on "EXISTS(SELECT 1 FROM customer)" if
>>> there are many employees. [...]
>>
>> I believe that the scan stops on the first row it finds, because the
>> EXITS() clause is met.
>
> Hmmm... That is not so clear from "EXPLAIN" output:

You need to use a better test case...

> explain analyze select exists(select 1 from generate_series(1,99999) gs);
>                                                           QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------
>  Result  (cost=0.01..0.02 rows=1 width=1) (actual time=26.278..26.278 rows=1 loops=1)
>    InitPlan 1 (returns $0)
>      ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=0) (actual time=26.271..26.271 rows=1 loops=1)
>  Planning time: 6.568 ms
>  Execution time: 48.917 ms
> (5 rows)

In any case, +1 for not promoting count(*) <> 0; that's a really, really 
bad way to test for existence.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers