Re: explicit cursor vs. for loop in pl/pgsql

"David Parker" <dparker@tazznetworks.com> writes:
> I know from the documentation that the FOR implicitly opens a cursor,
> but I'm wondering if there would be any performance advantages to
> explicitly declaring a cursor and moving through it with FETCH commands?

AFAICS it'd be exactly the same.  Might as well stick with the simpler
notation.

> I have to use the ORDER BY, so I imagine I'm taking the hit of
> processing all the records in the table anyway, regardless of how many I
> ultimately fetch.

Not if the ORDER BY can be implemented using an index.  Perhaps what you
need is to make sure that an indexscan gets used.

> The nature of the data is that chunksize doesn't necessarily match up
> one-for-one with rows, so I can't use it as a LIMIT value.

Can you set an upper bound on how many rows you need?  If you can put a
LIMIT into the select, it'll encourage the planner to use an indexscan,
even if you break out of the loop before the limit is reached.

            regards, tom lane