Re: Slow query in trigger function - Mailing list pgsql-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.