Re: Fast distinct not working as expected

Hi,
>
> That is not equivalent to a distinct. There must be more to it than that.
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).
>
>
>
>
>
>
> My best guess would be that the index got stuffed full of entries for
> rows that are not visible, either because they are not yet committed,
> or have been deleted but are not yet vacuumable. Do you have any
> long-lived transactions?
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)

>
>
>
> - postgresql Version 8.4
>
>
> Newer versions have better diagnostic tools. An explain (analyze,
> buffers) would be nice, especially with track_io_timing on.
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