|
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.
|
