Re: large query by offset and limt

From: Ge Cong <gecong(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: large query by offset and limt
Date: 2008-05-03 20:53:36
Message-ID: f06ed29f-c42d-4f93-9ae8-aaff3782f85f@q1g2000prf.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thank you very much. Could you show me how to do it in JDBC?

Craig Ringer wrote:
> finecur wrote:
> > Hi, I am ruuning a database behind a webserver and there is a table
> > which is huge. I need to pull data from this table and send to user
> > through http. If I use
> >
> > select * from huge_table where userid = 100
> >
> > It will return millions of records which exhuasts my server's memory.
>
> > Is there a better way to do this?
>
> Consider using database cursors. Set up by beginning a transaction and
> issuing the statement:
>
> DECLARE huge_table_curs CURSOR FOR
> SELECT * FROM huge_table WHERE userid = 100;
>
> ... then to get results, just execute:
>
> FETCH 1000 FROM huge_table_curs;
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Anderson 2008-05-03 23:05:30 Problems in queries
Previous Message Bruno Wolff III 2008-05-03 20:51:29 Interesting comments about fsync on Linux