Query return speed is fine but fetch is very slow

PostgreSQL 14

PHP 7.4

I have a PHP call that returns a handle to a scrollable cursor (PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) with about 760,000 records. The handle is being returned in a reasonable amount of time. However, once I have the handle back, doing nothing but fetching each record in a loop is taking over 12 minutes. I have tried it as a forward-only cursor with similar results. I am on high performance processors and have plenty of memory. Each record has 181 numeric fields. How can I improve the performance of this?

$first = true;
while($rec = $handle->fetch(PDO::FETCH_ASSOC, $first ? PDO::FETCH_ORI_FIRST : PDO::FETCH_ORI_NEXT))
{
    $first = false;
}

Updates to answer questions:

Network distance between client and server?

Zero. Both are on localhost.

Where is the return time being measured in the database layer or an application frontend?

Measured from the time the query is executed to when the handle is returned. Getting the handle is fine. It’s the fetch loop itself that is taking forever once it starts. PHP is measuring the fetch loop execution time.

Do you need to fetch one by one or can you fetch in batches?

I could fetch in batches and process the batches one by one, but the base query is very heavy and running it repeatedly to get to an offset would not be good.

Answer

Try running your statement on a psql command line.

If it is slow there as well, you have to use EXPLAIN (ANALYZE, BUFFERS) to understand why (and add the execution plan to the question for further help).

If it is fast in psql, the problem are either the 760000 client-server round trips or that you didn’t set cursor_tuple_fraction to 1.0.

Advertisement

Answer