'Are PostgreSQL scroll cursor on views not performant?

I am trying to have a scroll cursor retrieve rows from a view in postgresSQL. Before this I used the cursor on the real table to and it worked just fine, but now it simply takes an extremely long time (i.e. it has not completed yet, it is not feasible at this speed) to even retrieve a small number of rows from the table. The only difference I can think of is: The view is based on a join (which is the reason why I started using a view) and the view is in another schema (should not make a difference should it?) Am I understanding views incorrectly? Does the join still have to happen before I can start retrieving rows? Just doing a SELECT with a limit clause on the view returns instantaneous though.

My code is simply:

BEGIN;
DECLARE somecurs SCROLL CURSOR FOR SELECT text from notmyschema.someview;
FETCH FORWARD 2000 FROM somecurs;
--- does not return ---
sqldatabasepostgresql


Solution 1:[1]

Here's a good way to debug this:

  1. Run explain analyze %your query% on a table.
  2. Run explain analyze %your query% on a view.
  3. Compare those, maybe the view query is missing an index or something.

I highly suggest also using https://explain.depesz.com/ to learn what are the slowest parts of your queries, and how to fix that: each operation like SeqScan is clickable and will bring you to an explanation article.

You can also edit your Q with query plans added so people can help you more.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Evgeniy Chekan

Related Questions