Re: Fast distinct not working as expected

[Franck Routier <franck.routier@xxxxxxxxx>]

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