Re: [PERFORM] Takes too long to fetch the data from database

On Tue, May 09, 2006 at 09:24:15 +0530, soni de <[EMAIL PROTECTED]> wrote: > > EXPLAIN > pdb=# EXPLAIN ANALYZE select * from wan where kname = 'pluto' order by stime > limit 50; > NOTICE: QUERY PLAN: > > Limit (cost=3515.32..3515.32 rows=50 width=95) (actual time= > 230492.69..230493.07 rows=

Re: [PERFORM] Takes too long to fetch the data from database

select version(); to check.       -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of soni deSent: Thursday, April 20, 2006 11:42 PM To: Merlin MoncureCc: pgsql-performance@postgresql.orgSubject: Re: [PERFORM] Takes too long to fetch the data from database  

Re: [PERFORM] Takes too long to fetch the data from database

On Fri, Apr 21, 2006 at 09:44:25AM -0400, Merlin Moncure wrote: > 2nd 50: > select * from t where j >= j1 and (j > j1 or k > k1) order by j, k limit 50; > 3 fields: > select * from t where i >= i1 and (i > i1 or j >= j1) and (i > i1 or j > > k1 or k > k1) order by i,j,k limit 50; Note that in 8.2

Re: [PERFORM] Takes too long to fetch the data from database

On Fri, Apr 21, 2006 at 10:12:24 +0530, soni de <[EMAIL PROTECTED]> wrote: > 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). You can do this efficiently, if stime h

Re: [PERFORM] Takes too long to fetch the data from database

On 4/21/06, soni de <[EMAIL PROTECTED]> wrote: > > 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). aha! you need to implement a 'sliding window' query. simplest is

Re: [PERFORM] Takes too long to fetch the data from database

- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 s

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' orde

Re: [PERFORM] Takes too long to fetch the data from database

> 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

Re: [PERFORM] Takes too long to fetch the data from database

On Thu, Apr 20, 2006 at 11:07:31 +0530, soni de <[EMAIL PROTECTED]> wrote: > Please provide me some help regarding how could I use cursor in following > cases? : > > I want to fetch 50 records at a time starting from largest stime. > > SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900; So

Re: [PERFORM] Takes too long to fetch the data from database

  Please provide me some help regarding how could I use cursor in following cases? :   I want to fetch 50 records at a time starting from largest stime.   Total no. of records in the "wan" table:   82019   pdb=# \d wan   Table "wan"    Column    |   Type   

Re: [PERFORM] Takes too long to fetch the data from database

Richard Huxton writes: > soni de wrote: >> NOTICE: QUERY PLAN: >> Sort (cost=17.13..17.13 rows=1 width=16) (actual >> time=619140.18..619140.29rows >> =288 loops=1) >> -> Index Scan using lan_pkey on lan (cost=0.00..17.12 rows=1 width=16) >> (actual time=7564.44..619121.61 rows=288 loops=1) >

Re: [PERFORM] Takes too long to fetch the data from database

> pdb=# explain analyze SELECT sdate, stime, rbts from lan WHERE ( > > ( bname = 'pluto' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate > > >= '2004-07-21' ) AND ( sdate <= '2004-07-21' ) ) ) ORDER BY sdate, stime > ; this query would benefit from an index on pluto, cno, pno, sdate create i

Re: [PERFORM] Takes too long to fetch the data from database

soni de wrote: I have flushed the database, so currently records in the "lan" table are: 665280 but records can be increased more than 1GB and in that case it takes more than 1 hour Below is explain analyze output taken from the table having 665280 records pdb=# explain analyze SELECT sdate, s

Re: [PERFORM] Takes too long to fetch the data from database

  I have flushed the database, so currently records in the "lan" table are: 665280 but records can be increased more than 1GB and in that case it takes more than 1 hour   Below is explain analyze output taken from the table having 665280 records   pdb=# explain analyze SELECT sdate, stime, rbts fro

Re: [PERFORM] Takes too long to fetch the data from database

Rajesh Kumar Mallah wrote: what is the query ? use LIMIT or a restricting where clause. You could also use a cursor. Joshua D. Drake regds mallah. On 4/10/06, *soni de* < [EMAIL PROTECTED] > wrote: Hello, I have difficulty in fetching the records f

Re: [PERFORM] Takes too long to fetch the data from database

what is the query ?use LIMIT or a restricting where clause.regdsmallah.On 4/10/06, soni de < [EMAIL PROTECTED]> wrote:Hello,   I have difficulty in fetching the records from the database. Database table contains more than 1 GB data. For fetching the records it is taking more the 1 hour and that's w

[PERFORM] Takes too long to fetch the data from database

Hello,   I have difficulty in fetching the records from the database. Database table contains more than 1 GB data. For fetching the records it is taking more the 1 hour and that's why it is slowing down the performance. please provide some help regarding improving the performance and how do I run q