From: Guido Niewerth <gniewerth@ocsgmbh.com>
To: Guido Niewerth <gniewerth@ocsgmbh.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow query in trigger function
Date: Tue, 3 Nov 2015 10:58:26 +0000
Message-ID: <83359cf9670c4026bbef97cac9005f43@EX2k13.ocsnet.local> (raw)
In-Reply-To: <d9752446b5164156836d9bb97bacfe58@EX2k13.ocsnet.local>
References: <d9752446b5164156836d9bb97bacfe58@EX2k13.ocsnet.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 = old.key) => sequential scan
IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key LIMIT 1) => sequential scan
IF NOT EXISTS (SELECT max( 1 ) FROM custom_data WHERE key = old.key) => sequential scan
After breaking up the code into two statements the execution planer uses the index scan:
result INTEGER;
SELECT 1 FROM custom_data where key = old.key INTO result;
IF result ISNULL THEN
...
END IF;
To me it looks like the execution planer does not choose the optimal strategy. Even small changes in the function body make the execution planer use the 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gniewerth@ocsgmbh.com
Subject: Re: Slow query in trigger function
In-Reply-To: <83359cf9670c4026bbef97cac9005f43@EX2k13.ocsnet.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox