X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6C5259FA15C for ; Tue, 9 May 2006 00:54:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 87753-10 for ; Tue, 9 May 2006 00:54:17 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.180]) by postgresql.org (Postfix) with ESMTP id 095EE9F9D82 for ; Tue, 9 May 2006 00:54:17 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id c63so1561646pyc for ; Mon, 08 May 2006 20:54:15 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WVP8Y5aVNmlUdewKn2mLslQVpx0y8ZB/JQEzOhRdTilc+4HiRYHG7XZ8EMAWulqPdJYGWfvO7z3aOPjzCgyTSfSLooeylRO5N9ECyUN2QzGaLkIqQ96Xq0YNSt1BWZSDzNY6SGOBPYffQoHbqwkPCUE6T5vRKYGj8G9cfFjXeEo= Received: by 10.35.88.17 with SMTP id q17mr781658pyl; Mon, 08 May 2006 20:54:15 -0700 (PDT) Received: by 10.35.8.14 with HTTP; Mon, 8 May 2006 20:54:15 -0700 (PDT) Message-ID: <9f2e40a90605082054g6dd3e4f5ne3faf631e4792116@mail.gmail.com> Date: Tue, 9 May 2006 09:24:15 +0530 From: "soni de" To: "Dave Dutcher" Subject: Re: Takes too long to fetch the data from database Cc: pgsql-performance@postgresql.org In-Reply-To: <007801c66547$35e26270$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_38245_11902809.1147146855095" References: <9f2e40a90604202142x5adbe6f0r7a993f8319619cc7@mail.gmail.com> <007801c66547$35e26270$8300a8c0@tridecap.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/166 X-Sequence-Number: 18953 ------=_Part_38245_11902809.1147146855095 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello, I have tried the query SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT 50; and it is working great. EXPLAIN ANALYSE of the above query is: pdb=3D# EXPLAIN ANALYZE select * from wan order by stime desc limit 50 ; NOTICE: QUERY PLAN: Limit (cost=3D0.00..12.10 rows=3D50 width=3D95) (actual time=3D24.29..50.2= 4 rows=3D50 loops=3D1) -> Index Scan Backward using wan_pkey on wan=20 (cost=3D0.00..19983.31rows=3D82586 width=3D95) (actual time=3D 24.28..50.14 rows=3D51 loops=3D1) Total runtime: 50.55 msec EXPLAIN Now I am facing another problem, If I use where clause is select query it i= s taking too much time. Can you please help me on this. Explain analyze are follows: pdb=3D# EXPLAIN ANALYZE select count(1) from wan where kname =3D 'pluto'; NOTICE: QUERY PLAN: Aggregate (cost=3D3507.84..3507.84 rows=3D1 width=3D0) (actual time=3D 214647.53..214647.54 rows=3D1 loops=3D1) -> Seq Scan on wan (cost=3D0.00..3507.32 rows=3D208 width=3D0) (actual = time=3D 13.65..214599.43 rows=3D18306 loops=3D1) Total runtime: 214647.87 msec EXPLAIN pdb=3D# EXPLAIN ANALYZE select * from wan where kname =3D 'pluto' order by = stime limit 50; NOTICE: QUERY PLAN: Limit (cost=3D3515.32..3515.32 rows=3D50 width=3D95) (actual time=3D 230492.69..230493.07 rows=3D50 loops=3D1) -> Sort (cost=3D3515.32..3515.32 rows=3D208 width=3D95) (actual time=3D 230492.68..230493.00 rows=3D51 loops=3D1) -> Seq Scan on wan (cost=3D0.00..3507.32 rows=3D208 width=3D95) (= actual time=3D0.44..229217.38 rows=3D18306 loops=3D1) Total runtime: 230631.62 msec EXPLAIN pdb=3D# EXPLAIN ANALYZE SELECT * FROM wan WHERE stime >=3D 20123 AND stime= <=3D 24000 ORDER BY stime limit 50; NOTICE: QUERY PLAN: Limit (cost=3D0.00..2519.70 rows=3D50 width=3D95) (actual time=3D7346.74..7351.42rows=3D50 loops=3D1) -> Index Scan using wan_pkey on wan (cost=3D0.00..20809.17 rows=3D413 width=3D95) (actual time=3D7346.73..7351.32 rows=3D51 loops=3D1) Total runtime: 7351.71 msec EXPLAIN for above queries if I use desc order then the queries takes too much time= . I am not getting for the above queries how do I increase the speed. Postgresql version is 7.2.3 total no. of records: 5700300 On 4/21/06, Dave Dutcher wrote: > > 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 5= 0 > 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 ta= king > 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 > > > ------=_Part_38245_11902809.1147146855095 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

Hello,
 
I have tried the query SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIM= IT 50; and it is working great.
EXPLAIN ANA= LYSE of the above query is:
pdb=3D# EXPLAIN ANALYZE select * from = wan order by stime desc limit 50 ;
NOTICE:  QUERY PLAN:

Limit  (cost=3D0.00..12.10 rows=3D50 width=3D95) (actual time=3D24.= 29..50.24 rows=3D50 loops=3D1)
  ->  Index Scan Backward us= ing wan_pkey on wan  (cost=3D0.00..19983.31 rows=3D82586 width=3D95) (= actual time=3D24.28..50.14 rows=3D51 loops=3D1)
Total runtime: 50.55 msec

EXPLAIN

 
Now I am fa= cing another problem, If I use where clause is select query it is taking to= o much time. Can you please help me on this.
 
Explain ana= lyze are follows:
pdb=3D# EXPLAIN ANALYZE select count(1= )  from wan where kname =3D 'pluto';
NOTICE:  QUERY PLAN:

Aggregate  (cost=3D3507.84..3507.84 rows=3D1 width=3D0) (actual tim= e=3D214647.53..214647.54 rows=3D1 loops=3D1)
  ->  Seq Scan= on wan  (cost=3D0.00..3507.32 rows=3D208 width=3D0) (actual time=3D13= .65..214599.43 rows=3D18306 loops=3D1)
Total runtime: 214647.87 msec

EXPLAIN
pdb=3D# EXPLAIN ANALYZE select * from wan where kname =3D 'pl= uto' order by stime limit 50;
NOTICE:  QUERY PLAN:

Limit  (cost=3D3515.32..3515.32 rows=3D50 width=3D95) (actual time= =3D230492.69..230493.07 rows=3D50 loops=3D1)
  ->  Sort&nbs= p; (cost=3D3515.32..3515.32 rows=3D208 width=3D95) (actual time=3D230492.68= ..230493.00 rows=3D51 loops=3D1)
      &nb= sp; ->  Seq Scan on wan  (cost=3D 0.00..3507.32 rows=3D208 width=3D95) (actual time=3D0.44..229217.38 rows=3D= 18306 loops=3D1)
Total runtime: 230631.62 msec

EXPLAIN
pdb=3D# EXPLAIN ANALYZE SELECT * FROM wan  WHERE stime &= gt;=3D 20123 AND stime <=3D 24000 ORDER BY stime limit 50;
NOTICE:&nb= sp; QUERY PLAN:

Limit  (cost=3D0.00..2519.70 rows=3D50 width=3D95) (actual time=3D7= 346.74..7351.42 rows=3D50 loops=3D1)
  ->  Index Scan using= wan_pkey on wan  (cost=3D0.00..20809.17 rows=3D413 width=3D95) (actua= l time=3D7346.73..7351.32 rows=3D51 loops=3D1)
Total runtime: 7351.71 msec

EXPLAIN

for above queries if I use desc order then the queries takes&n= bsp; too much time.

I am not ge= tting for the above queries how do I increase the speed.
 
Postgresql version is 7.2.3
total no. of records: 5700300
 
On 4/21/06, = Dave Dutcher <dave@tridecap.com= > wrote:

I've never used a cursor in P= ostgres, 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 t= hat getting the first page is slow.   A cursor isn't going to be any faster at getting the fi= rst page than OFFSET/LIMIT is.

 

Did you try Bruno's suggestion of:

 

SE= LECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT 50;<= /font>

&n= bsp;

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

 

 

 


------=_Part_38245_11902809.1147146855095--