Postgres sometimes takes longer to fetch from a cursor

Question

I am fetching from a cursor in PL/PgSQL, and sometimes it takes longer (1 second) to do a fetch compared with the rest of them that only take some milliseconds.

I don't know what to check, any ideas? How to improve this behaviour?

I captured a video that better shows this behavior: https://youtu.be/C8h19WGM8XE I understand why sometimes there is a wait "After" the fetch. But I don't understand the other.

As you can see, there is a NOTICE just before and after the fetch, and I haven't found a reason to explain this.

The relevant code:

OPEN notes_on_day FOR EXECUTE (
   'SELECT /* Notes-staging */
      c.note_id id_note, c.sequence_action sequence_action,
      n.created_at created_at, o.id_user created_id_user, n.id_country id_country,
      c.sequence_action seq, c.event action_comment, c.id_user action_id_user,
      c.created_at action_at, t.body
   FROM note_comments c
      JOIN notes n
         ON (c.note_id = n.note_id)
      JOIN note_comments o
         ON (n.note_id = o.note_id AND o.event = ''opened'')
      JOIN note_comments_text t
         ON (c.note_id = t.note_id AND c.sequence_action = t.sequence_action)
      JOIN dwh.dimension_days dd
         ON (DATE(c.created_at) = dd.date_id)
   WHERE c.created_at > ''' || max_processed_timestamp || '''
     AND dd.date_id = ''' || DATE(max_processed_timestamp) -- Notes for the same date.
                      || '''
     AND dd.year = ''' || EXTRACT(YEAR FROM max_processed_timestamp) || '''
   ORDER BY c.note_id, c.id');
LOOP
   RAISE NOTICE 'before fetch % - %', CLOCK_TIMESTAMP(), m_count;
   FETCH notes_on_day INTO rec_note_action;
   RAISE NOTICE 'after fetch % - %', CLOCK_TIMESTAMP(), m_count;
   -- Exit when no more rows to fetch.
   EXIT WHEN NOT FOUND;
   [...]
END LOOP;

CLOSE notes_on_day;

Answer 1

That is normal. The PostgreSQL server sends a result row as soon as it is computed, so if it takes longer to calculate a result row, you'll have to wait longer.

In your special case, I would assume that the first FETCH will take long, and subsequent FETCHes will be shorter. That's probably because PostgreSQL has to complete sorting the rows for the ORDER BY clause before it can return the first result row, but once the rows are sorted, the next result rows can be completed quickly.

For an analysis that does not depend on guesswork, you could use auto_explain to see the actual query execution plans.