Re: Slow query in trigger function

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(at)ocsgmbh(dot)com
Web: http://www.ocsgmbh.com

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