X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id B31B211F66F5 for ; Fri, 21 Apr 2006 10:26:50 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.598 X-Spam-Level: X-Spam-Status: No, score=-2.598 required=5 tests=[BAYES_00=-2.599, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id 7tsv1ko6BFPD for ; Fri, 21 Apr 2006 10:26:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id 9E48911F66F1 for ; Fri, 21 Apr 2006 10:26:35 -0300 (ADT) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [63.226.156.118] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 88364541; Fri, 21 Apr 2006 06:26:34 -0700 From: "Dave Dutcher" To: "'soni de'" Cc: Subject: Re: Takes too long to fetch the data from database Date: Fri, 21 Apr 2006 08:26:33 -0500 Message-ID: <007801c66547$35e26270$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0079_01C6651D.4D0C5A70" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-reply-to: <9f2e40a90604202142x5adbe6f0r7a993f8319619cc7@mail.gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Importance: Normal X-Archive-Number: 200604/500 X-Sequence-Number: 18584 This is a multi-part message in MIME format. ------=_NextPart_000_0079_01C6651D.4D0C5A70 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I've never used a cursor in Postgres, but I don't think it will help you a lot. In theory cursors make it easier to do paging, but your main problem is that getting the first page is slow. A cursor isn't going to be any faster at getting the first page than OFFSET/LIMIT is. Did you try Bruno's suggestion of: SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT 50; You should run an EXPLAIN ANALYZE on that query to see if it is using an index scan. Also what version of Postgres are you using? You can run select version(); to check. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of soni de Sent: Thursday, April 20, 2006 11:42 PM To: Merlin Moncure Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Takes too long to fetch the data from database I don't want to query exactly 81900 rows into set. I just want to fetch 50 or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows starting from last to end). if we fetched sequentially, there is also problem in fetching all the records (select * from wan where kname='pluto' order by stime) it is taking more than 4~5 minutes. tried it on same table having more than 326054 records. On 4/20/06, Merlin Moncure wrote: > SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900; you need to try and solve the problem without using 'offset'. you could do: BEGIN; DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime; FETCH ABSOLUTE 81900 in crs; FETCH 49 in crs; CLOSE crs; COMMIT; this may be a bit faster but will not solve the fundamental problem. the more interesting question is why you want to query exactly 81900 rows into a set. This type of thinking will always get you into trouble, absolute positioning will not really work in a true sql sense. if you are browsing a table sequentially, there are much better methods. merlin ------=_NextPart_000_0079_01C6651D.4D0C5A70 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I’ve never used a cursor in = Postgres, but I don’t think it will help you = a lot.  In theory cursors = make it easier to do paging, but your main problem is that getting the first = page is slow.  A cursor isn’t = going to be any faster at getting the first page than OFFSET/LIMIT = is.

 

Did you try Bruno’s = suggestion of:

 

SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT 50;

 

You should run an EXPLAIN ANALYZE = on that query to see if it is using an index scan.  Also what version of Postgres are you using?  You can run select = version(); to check.

 

 

 

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of soni de
Sent: = Thursday, April 20, = 2006 11:42 PM
To: Merlin Moncure
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] = Takes too long to fetch the data from database

 

I don't want to query exactly 81900 rows into set. I just want = to fetch 50 or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 = rows starting from last to end).

 

if we fetched sequentially, there is also problem in fetching = all the records (select * from wan where kname=3D'pluto' order by stime) it = is taking more than 4~5 minutes. tried it on same table having more than = 326054 records.


 

On 4/20/06, Merlin = Moncure <mmoncure@gmail.com> = wrote:

> SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900;

you need to try and solve the problem without using = 'offset'.  you could do:
BEGIN;
DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime;
FETCH ABSOLUTE 81900 in crs;
FETCH 49 in crs;
CLOSE crs;
COMMIT;

this may be a bit faster but will not solve the fundamental problem. =

the more interesting question is why you want to query exactly 81900
rows into a set.  This type of thinking will always get you = into
trouble, absolute positioning will not really work in a true sql
sense.  if you are browsing a table sequentially, there are = much
better methods.

merlin

 

------=_NextPart_000_0079_01C6651D.4D0C5A70--