Re: Cursor With_Hold Performance Workarounds/Optimization

On Thu, Oct 19, 2017 at 03:20:48PM +0200, Leon Winter wrote:
> Hi,
>
> I originally brought up this issue on the pgsql-performance mailing list [^] to
> no avail so I am trying again here.
>
> During implementation of a runtime environment and the adjoining database
> abstraction layer I noticed (like many before me [0] and as correctly mentioned
> in the documentation) the sizeable performance impact of declaring a cursor
> "with hold" for queries with large result sets.
>
> Our use case very often looks like this:
>
> open cursor for select from table1
> loop
> { fetch some entries from cursor
> update table2
> commit
> }

This seems like a very odd construct based on ideas about databases
that aren't actually true of PostgreSQL, e.g. that joins are
expensive, or that some substantial benefit comes of committing at
some higher frequency than the logical transaction.

What other things did you try, and how did they fail? In particular,
what happened when you used

UPDATE table2
SET [things based on table1]
FROM table1 [qualified] JOIN table2 ON ([conditions])

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate