Обсуждение: Alternate or Optimization for with hold cursor
Currently , I am using "With hold" cursor. In our case , the With hold cursor is used to fetch the next record of the given primary key . The performance is very slow for large data set. Can you provide me some alternative ways like having own copy of table , or optimization for With hold cursor? Thanks and Regards, S.Sangeetha -- View this message in context: http://postgresql.nabble.com/Alternate-or-Optimization-for-with-hold-cursor-tp5903211.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On May 12, 2016, at 4:57 AM, sangeetha <tune2sangee@gmail.com> wrote: Currently , I am using "With hold" cursor. In our case , the With hold cursor is used to fetch the next record of the given primary key . Can you explain your use case. If i understand with hold correctly, it is typically used to preserve locks even after commit, so as to get a consistent view of data. The performance is very slow for large data set. Can you provide me some alternative ways like having own copy of table , or optimization for With hold cursor? Thanks and Regards, S.Sangeetha -- View this message in context: http://postgresql.nabble.com/Alternate-or-Optimization-for-with-hold-cursor-tp5903211.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Not sure what you trying to achieve, you could give a try for Materialized Views[1], see would this help you or not. 1. http://www.postgresql.org/docs/9.3/static/rules-materializedviews.html 2. http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html Regards, Amul Sul -- View this message in context: http://postgresql.nabble.com/Alternate-or-Optimization-for-with-hold-cursor-tp5903211p5903233.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>From: Sangeetha Sent: Thursday, May 12, 2016 1:58 AM Currently , I am using "With hold" cursor. In our case , the With hold cursor is used to fetch the next record of the given primary key . The performance is very slow for large data set. Can you provide me some alternative ways like having own copy of table , or optimization for With hold cursor? Thanks and Regards, S.Sangeetha< ================== Cursors are the last tool I would ever grab out of my sql toolbox (aka, I never use one) - it converts the enormous power of a relational database engine into "RBAR" (row by agonizing row). For a large dataset in particular, you are killing the server since the entire resultset must be retained in working memory for the duration of the query as it peels off one row at a time from that resultset OR if it's larger than your ram, you'll be paging to disk constantly. And since you're working on a single row at time, it will take forever. Convert the cursor into a normal query and you should see BIG (10-1000x) gains in speed. A cursor can always be converted to normal sql...always...it's not always easy but it's always worth the effort. Mike Sofen