Hi,
Indeed, this query is used in a loop:
CREATE OR REPLACE FUNCTION small_distinct(IN tablename character varying, IN fieldname character varying, IN sample anyelement DEFAULT ''::character varying)
RETURNS SETOF anyelement AS
$BODY$
BEGIN
EXECUTE 'SELECT '||fieldName||' FROM '||tableName||' ORDER BY '||fieldName
||' LIMIT 1' INTO result;
WHILE result IS NOT NULL LOOP
RETURN NEXT;
EXECUTE 'SELECT '||fieldName||' FROM '||tableName
||' WHERE '||fieldName||' > $1 ORDER BY ' || fieldName || ' LIMIT 1'
INTO result USING result;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
Since we have the problem, some iteration of the query are still quick (< 1ms), but others are long (> 5s).
There has been a delete on the table (about 20% of the records). Then a manual VACUUM.
We have recreated the index, but it did not help.
In the explain analyze output, the index scan begins at 5798.912. What can be happening before that ?
Index Scan using vsn_idx on dwhinv (cost=0.00..302591122.05
rows=267473826 width=12) (actual time=5798.912..5798.912 rows=1 loops=1)
(Notice the delay is not planning itself, as explain is instantaneous)
Yep, we certainly would like to, but this is a distant prod box, with no access to an online upgrade source, and no planned upgrade for now :-((
Regards,
Franck