Re: Differences when calling query inside and outside cursor

Carlos Henrique Reimer wrote:
> We're facing a weird performance problem in one of our PostgreSQL servers running 8.0.26.

Ouch.
8.0 has been out of support since October 2010, and
I am afraid that might be a problem for you.

> What can explain the difference between calling same query inside and outside a cursor? If we run the
> query outside a cursor we got a response time of 755ms and 33454ms if we call the same query inside a
> cursor.

The planner estimates that only 10% of the rows
from the cursor will be fetched. That favors
plan that deliver the first rows quickly.

This percentage has been added as a configuration
parameter in 8.4 (cursor_tuple_fraction).
You would set this parameter to 1.0 to get the same
plans with or without cursors.

> I suspect the query called inside the cursor is using a different plan than the same query outside a
> cursor. Is there a way to confirm this suspicion?

You could try
EXPLAIN DECLARE cur1 CURSOR FOR ....

Yours,
Laurenz Albe