Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZtZIY-0005sE-FF for pgsql-performance@arkaria.postgresql.org; Tue, 03 Nov 2015 10:58:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZtZIX-0002Rd-Dc for pgsql-performance@arkaria.postgresql.org; Tue, 03 Nov 2015 10:58:41 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZtZIW-0002RW-NH for pgsql-performance@postgresql.org; Tue, 03 Nov 2015 10:58:40 +0000 Received: from mail.ocsgmbh.com ([130.180.49.58]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZtZIP-0002Z4-Bv for pgsql-performance@postgresql.org; Tue, 03 Nov 2015 10:58:39 +0000 Received: from [10.80.80.117] (port=10287 helo=EX2k13.ocsnet.local) by mail.ocsgmbh.com with esmtps (TLSv1:AES256-SHA:256) (Exim 4.82_1-5b7a7c0-XX) (envelope-from ) id 1ZtZIJ-0000J4-1b for pgsql-performance@postgresql.org; Tue, 03 Nov 2015 11:58:27 +0100 Received: from EX2K13.ocsnet.local (10.80.80.117) by EX2k13.ocsnet.local (10.80.80.117) with Microsoft SMTP Server (TLS) id 15.0.847.32; Tue, 3 Nov 2015 11:58:27 +0100 Received: from EX2K13.ocsnet.local ([fe80::5995:67ac:bffa:e7bf]) by EX2k13.ocsnet.local ([fe80::5995:67ac:bffa:e7bf%12]) with mapi id 15.00.0847.040; Tue, 3 Nov 2015 11:58:26 +0100 From: Guido Niewerth To: Guido Niewerth , "pgsql-performance@postgresql.org" Subject: Re: Slow query in trigger function Thread-Topic: Slow query in trigger function Thread-Index: AdEVV2md0gPsAGXpQliVPXR/PhjpTgAzoNhw Date: Tue, 3 Nov 2015 10:58:26 +0000 Message-ID: <83359cf9670c4026bbef97cac9005f43@EX2k13.ocsnet.local> References: In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.80.81.95] x-gfi-smtp-submission: 1 x-gfi-smtp-hellodomain: EX2K13.ocsnet.local x-gfi-smtp-remoteip: 10.80.80.117 Content-Type: multipart/alternative; boundary="_000_83359cf9670c4026bbef97cac9005f43EX2k13ocsnetlocal_" MIME-Version: 1.0 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_83359cf9670c4026bbef97cac9005f43EX2k13ocsnetlocal_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable These are the queries I used to get the execution planer use the index scan= instead of the sequential scan: IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key =3D old.key) =3D> sequen= tial scan IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key =3D old.key LIMIT 1) =3D= > sequential scan IF NOT EXISTS (SELECT max( 1 ) FROM custom_data WHERE key =3D old.key) =3D>= sequential scan After breaking up the code into two statements the execution planer uses th= e index scan: result INTEGER; SELECT 1 FROM custom_data where key =3D old.key INTO result; IF result ISNULL THEN ... END IF; To me it looks like the execution planer does not choose the optimal strate= gy. Even small changes in the function body make the execution planer use t= he slow sequential scan. Guido Niewerth 25 years inspired synergy OCS Optical Control Systems GmbH Wullener Feld 24 58454 Witten Germany Tel: +49 (0) 2302 95622-0 Fax: +49 (0) 2302 95622-33 Email: gniewerth@ocsgmbh.com Web: http://www.ocsgmbh.com HRB 8442 (Bochum) | VAT-ID: DE 124 084 990 Directors: Hans Gloeckler, Fatah Najaf, Merdan Sariboga --_000_83359cf9670c4026bbef97cac9005f43EX2k13ocsnetlocal_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

These are the queries = I used to get the execution planer use the index scan instead of the sequen= tial scan:

 

IF NOT EXISTS (SELECT = 1 FROM custom_data WHERE key =3D old.key) =3D> sequential scan

IF NOT EXISTS (SELECT = 1 FROM custom_data WHERE key =3D old.key LIMIT 1) =3D> sequential scan

IF NOT EXISTS (SELECT = max( 1 ) FROM custom_data WHERE key =3D old.key) =3D> sequential scan

 

After breaking up the = code into two statements the execution planer uses the index scan:

 

result INTEGER;

SELECT 1 FROM custom_d= ata where key =3D old.key INTO result;

IF result ISNULL THEN<= o:p>

   ...<= /o:p>

END IF;

 

To me it looks like th= e execution planer does not choose the optimal strategy. Even small changes= in the function body make the execution planer use the slow sequential sca= n.

Guido Niewerth
25 years inspired synergy
OCS Optical= Control Systems GmbH
Wullener Feld 24
58454 Witten
Germany

Tel: +49 (0) 2302 95622-0
Fax: +49 (0) 2302 9562= 2-33
Email: gniewerth@ocsgmbh.com
= Web: http://www.ocsgmbh.com

HRB 8442 (Bochum) | VAT-ID: DE = 124 084 990
Directors: Hans Gloeckler,= Fatah Najaf, Merdan Sariboga


--_000_83359cf9670c4026bbef97cac9005f43EX2k13ocsnetlocal_--