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 out=
side 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 th=
an 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