X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BB83111F7081 for ; Thu, 20 Apr 2006 02:37:37 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 1.023 X-Spam-Level: * X-Spam-Status: No, score=1.023 required=5 tests=[AWL=-0.310, HTML_MESSAGE=0.001, RCVD_IN_BL_SPAMCOP_NET=1.332] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id zVPsCLnOiHCX for ; Thu, 20 Apr 2006 02:37:32 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from pproxy.gmail.com (pproxy.gmail.com [64.233.166.181]) by postgresql.org (Postfix) with ESMTP id AA91A11F6D59 for ; Thu, 20 Apr 2006 02:37:31 -0300 (ADT) Received: by pproxy.gmail.com with SMTP id x66so77714pye for ; Wed, 19 Apr 2006 22:37:31 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=PUckTOk+bPplsUuGjEaFcXgraBC/yIMPcvxfHaD8qNfYWyvU8g+2xzAsDkyh+Xb3T77//ndsw5xhd6YlIMYP2PgexzPgv0BeNmkewqSFEESRA/JWRHLFZdd637JrQ/CZnqtQd6b2ALOucUHFT8YMIgLdxuC5wD9em4b/cwEkKMY= Received: by 10.35.36.13 with SMTP id o13mr318401pyj; Wed, 19 Apr 2006 22:37:31 -0700 (PDT) Received: by 10.35.31.15 with HTTP; Wed, 19 Apr 2006 22:37:31 -0700 (PDT) Message-ID: <9f2e40a90604192237i5d3e8f0dl1c8b19887bc34e22@mail.gmail.com> Date: Thu, 20 Apr 2006 11:07:31 +0530 From: "soni de" To: "Merlin Moncure" , pgsql-performance@postgresql.org Subject: Re: Takes too long to fetch the data from database In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10547_5146525.1145511451596" References: <9f2e40a90604100021n1cb694d0lb95f318f800f33ee@mail.gmail.com> <9f2e40a90604110004u787716c2s22bdf53fe349326@mail.gmail.com> X-Archive-Number: 200604/436 X-Sequence-Number: 18520 ------=_Part_10547_5146525.1145511451596 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3D# \d wan Table "wan" Column | Type | Modifiers -------------+--------------------------+----------- stime | bigint | not null kname | character varying(64) | eid | smallint | rtpe | smallint | taddr | character varying(16) | ntime | bigint | Primary key: wan_pkey stime is the primary key. pdb=3D# SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900; pdb=3D# explain analyze SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900; NOTICE: QUERY PLAN: Limit (cost=3D17995.15..17995.15 rows=3D50 width=3D95) (actual time=3D 9842.92..9843.20 rows=3D50 loops=3D1) -> Sort (cost=3D17995.15..17995.15 rows=3D82016 width=3D95) (actual tim= e=3D 9364.56.. 9793.00 rows=3D81951 loops=3D1) -> Seq Scan on wan (cost=3D0.00..3281.16 rows=3D82016 width=3D95)= (actu al time=3D0.11..3906.29 rows=3D82019 loops=3D1) Total runtime: 10010.76 msec EXPLAIN pdb=3D# SELECT * FROM wan where kname=3D'pluto' ORDER BY stime LIMIT 50 OFFSET 8190= 0; pdb=3D# explain analyze SELECT * from wan where kname=3D'pluto' order by st= ime limit 50 offset 81900; NOTICE: QUERY PLAN: Limit (cost=3D3494.13..3494.13 rows=3D1 width=3D95) (actual time=3D9512.85..9512.85rows=3D0 loops=3D1) -> Sort (cost=3D3494.13..3494.13 rows=3D206 width=3D95) (actual time=3D 9330.74..9494.90 rows=3D27485 loops=3D1) -> Seq Scan on wan (cost=3D0.00..3486.20 rows=3D206 width=3D95) (= actual time=3D0.28..4951.76 rows=3D27485 loops=3D1) Total runtime: 9636.96 msec EXPLAIN SELECT * FROM wan where kname=3D'pluto' and rtpe=3D20 ORDER BY stime LIMIT = 50 OFFSET 81900; pdb=3D# explain analyze SELECT * from wan where kname=3D'pluto' and rtpe = =3D 20 order by stime limit 50 offset 81900; NOTICE: QUERY PLAN: Limit (cost=3D3691.25..3691.25 rows=3D1 width=3D95) (actual time=3D7361.50..7361.50rows=3D0 loops=3D1) -> Sort (cost=3D3691.25..3691.25 rows=3D1 width=3D95) (actual time=3D 7361.50..7361.50 rows=3D0 loops=3D1) -> Seq Scan on wan (cost=3D0.00..3691.24 rows=3D1 width=3D95) (ac= tual time=3D7361.30..7361.30 rows=3D0 loops=3D1) Total runtime: 7361.71 msec EXPLAIN pdb=3D# all the above queries taking around 7~10 sec. to fetch the last 50 records. I want to reduce this time because table is growing and table can contain more than 1 GB data then for 1 GB data above queries will take too much time. I am not getting how to use cursor to fetch records starting from last records in the above case offset can be any number (less than total no. of records). I have use following cursor, but it is taking same time as query takes. BEGIN; DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900; FETCH ALL in crs; CLOSE crs; COMMIT; On 4/11/06, Merlin Moncure wrote: > > > pdb=3D# explain analyze SELECT sdate, stime, rbts from lan WHERE ( > > > > ( bname =3D 'pluto' ) AND ( cno =3D 17 ) AND ( pno =3D 1 ) AND ( ( sda= te > > > > >=3D '2004-07-21' ) AND ( sdate <=3D '2004-07-21' ) ) ) ORDER BY sda= te, > stime > > ; > > this query would benefit from an index on > pluto, cno, pno, sdate > > create index Ian_idx on Ian(bname, cno, pno, sdate); > > > > pdb=3D# explain analyze SELECT ALL sdate, stime, rbts from lan WHERE ( = ( > > bname =3D 'neptune' ) AND ( cno =3D 17 ) AND ( pno =3D 1 ) AND ( ( sdat= e >=3D > > '2004-07-21' ) AND ( sdate <=3D '2004-07-21' ) ) ) ORDER BY sdate, st= ime > ; > > ditto above. Generally, the closer the fields in the where clause are > matched by the index, the it will speed up your query. > > Merlin > ------=_Part_10547_5146525.1145511451596 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
 

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 s= time.

 

Total no. of records in the "wan" table:   82019

 

pdb=3D# \d wan

    &n= bsp;            = ; Table "wan"

   Column    |      =      Type = ;            &n= bsp;  | Modifiers

-------------+--------------------------+-----------<= /font>

 stime          | bigint        &nbs= p;            &= nbsp;      |  not null

 kname        | character varying(64)      |

 eid             |= smallint     &n= bsp;                  &n= bsp;|

 rtpe            | small= int      &n= bsp;            = ;      |

 taddr          | character varying= (16)      |

 ntime         | bigint         &nbs= p;            &= nbsp;      |

 Primary key: = wan_pkey

 

stime is the primary key.

 

pdb=3D#

 

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

 

pdb=3D# explain analyze SELECT * FROM wan ORDER BY stime LIM= IT 50

 OFFSET 81900;=

NOTICE:  QUERY= PLAN:

 

Limit  (cost= =3D17995.15..17995.15 rows=3D50 width=3D95) (actual time=3D9842.92..9843.20

rows=3D50 loops=3D1)

  ->   Sort  (cost=3D1= 7995.15..17995.15 rows=3D82016 width=3D95) (actual time=3D9364.56..<= /font>

9793.00 rows=3D81951 loops=3D1)

    &n= bsp;   ->   Seq Scan on wan  (cost=3D0.00..3281.16 rows=3D82016 width=3D95) (actu

al time=3D0.11..3906.29 rows=3D82019 loops=3D1)

Total runtime: 10010.76 msec

 

EXPLAIN

pdb=3D#

 

 

SELECT * FROM wan where kname=3D'pluto' ORDER BY stime LIMIT= 50 OFFSET 81900;

 

pdb=3D# explain analyze SELECT * from wan where kname=3D'plu= to' order by stime limit 50 offset 81900;

NOTICE:  QUERY= PLAN:

 

Limit  (cost= =3D3494.13..3494.13 rows=3D1 width=3D95) (actual time=3D9512.85..9512.85 rows=3D0 loops=3D1)

  ->   Sort  (cost=3D3= 494.13..3494.13 rows=3D206 width=3D95) (actual time=3D9330.74..9494.90 rows= =3D27485 loops=3D1)

    &n= bsp;   ->   Seq Scan on wan  (cost=3D0.00..3486.20 rows=3D206 width=3D95) (actual time=3D0.28..4951.76= rows=3D27485 loops=3D1)

Total runtime: 9636.96 msec

 

EXPLAIN

 

SELECT * FROM wan where kname=3D'pluto' and rtpe=3D20 ORDER = BY stime LIMIT 50 OFFSET 81900;

 

pdb=3D# explain analyze SELECT * from wan where kname=3D'plu= to' and rtpe =3D 20 order by stime limit 50 offset 81900;

NOTICE:  QUERY= PLAN:

 

Limit  (cost= =3D3691.25..3691.25 rows=3D1 width=3D95) (actual time=3D7361.50..7361.50 rows=3D0 loops=3D1)

  ->   Sort  (cost=3D3= 691.25..3691.25 rows=3D1 width=3D95) (actual time=3D7361.50..7361.50 rows= =3D0 loops=3D1)

    &n= bsp;   ->   Seq Scan on wan  (cost=3D0.00..3691.24 rows=3D1 width=3D95) (actual time=3D7361.30..7361.3= 0 rows=3D0 loops=3D1)

Total runtime: 7361.71 msec

 

EXPLAIN

pdb=3D#

 

all the above queries taking around 7~10 sec. to fetch the l= ast 50 records. I want to reduce this time because table is growing and tab= le can contain more than 1 GB data then for 1 GB data above queries will ta= ke too much time.

 

I am not getting how to use cursor to fetch records starting= from last records in the above case offset can be any number (less than to= tal no. of records).=20

 

I have use following cursor, but it is taking same time as q= uery takes.

 

BEGIN;

DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime LIMI= T 50 OFFSET 81900;

FETCH ALL in crs;

CLOSE crs;

COMMIT;

 


 
On 4/11/06, = Merlin Moncure <mmoncure@gmail= .com> wrote:
> pdb=3D# explain analyze SEL= ECT sdate, stime, rbts from lan WHERE (
>
>  ( bname = =3D 'pluto' ) AND ( cno =3D 17 ) AND ( pno =3D 1 ) AND ( ( sdate
>
>  >=3D '2004-07-21' ) AND ( sdate <=3D '200= 4-07-21' ) )  )  ORDER BY sdate, stime
> ;
this query would benefit from an index on
pluto, cno, pno, sdate
create index Ian_idx on Ian(bname, cno, pno, sdate);


> pdb=3D# explain analyze SELECT ALL sdate, stime, rbts from= lan WHERE (  (
> bname =3D 'neptune' ) AND ( cno =3D 17 ) = AND ( pno =3D 1 ) AND ( ( sdate >=3D
> '2004-07-21' ) AND ( sdate = <=3D '2004-07-21' ) )  )  ORDER BY sdate, stime ;

ditto above.  Generally, the closer the fields in the whe= re clause are
matched by the index, the it will speed up your query.
=
Merlin

------=_Part_10547_5146525.1145511451596--