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 C2D3311F76CB for ; Tue, 11 Apr 2006 04:05:55 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.593 X-Spam-Level: X-Spam-Status: No, score=0.593 required=5 tests=[AWL=0.592, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id yKw+6Oi5NOEj for ; Tue, 11 Apr 2006 04:05:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from pproxy.gmail.com (pproxy.gmail.com [64.233.166.180]) by postgresql.org (Postfix) with ESMTP id 3F8F011F627B for ; Tue, 11 Apr 2006 04:05:27 -0300 (ADT) Received: by pproxy.gmail.com with SMTP id x66so1064860pye for ; Tue, 11 Apr 2006 00:05:27 -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=rx6dyEc0X+BwW9nqhjFrdek3YAjeQSnzxZ9YgDh4A0PY1lfLubzxwAWalmwgG4iLKzntOz+tOCDLdGHO44kj9/DCci0JGTxgR/C/iQzbJI86VSwZEeFYjKQ5+N4vuIpkbxR+DUJQi2j5tFM/FfnM0IbmDkFqVWlFhdT5nzVhxv4= Received: by 10.35.21.1 with SMTP id y1mr298156pyi; Tue, 11 Apr 2006 00:05:27 -0700 (PDT) Received: by 10.35.31.15 with HTTP; Tue, 11 Apr 2006 00:05:27 -0700 (PDT) Message-ID: <9f2e40a90604110005o101fe98enec2f8ac8f757c770@mail.gmail.com> Date: Tue, 11 Apr 2006 12:35:27 +0530 From: "soni de" To: "Joshua D. Drake" Subject: Re: Takes too long to fetch the data from database Cc: "Rajesh Kumar Mallah" , pgsql-performance@postgresql.org In-Reply-To: <443AA1D4.9010308@commandprompt.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2509_16979380.1144739127476" References: <9f2e40a90604100021n1cb694d0lb95f318f800f33ee@mail.gmail.com> <443AA1D4.9010308@commandprompt.com> X-Archive-Number: 200604/226 X-Sequence-Number: 18310 ------=_Part_2509_16979380.1144739127476 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3D# explain analyze SELECT 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 '2004-07-21' ) ) ) ORDER BY sdate, = stime ; NOTICE: QUERY PLAN: Sort (cost=3D17.13..17.13 rows=3D1 width=3D16) (actual time=3D619140.18..6= 19140.29rows =3D288 loops=3D1) -> Index Scan using lan_pkey on lan (cost=3D0.00..17.12 rows=3D1 width= =3D16) (ac tual time=3D7564.44..619121.61 rows=3D288 loops=3D1) Total runtime: 619140.76 msec EXPLAIN bsdb=3D# explain analyze SELECT DISTINCT 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 '2004-07-21' ) ) ) ORDER BY sdate, stime ; NOTICE: QUERY PLAN: Unique (cost=3D17.13..17.14 rows=3D1 width=3D16) (actual time=3D 610546.66..610564.31 rows=3D288 loops=3D1) -> Sort (cost=3D17.13..17.13 rows=3D1 width=3D16) (actual time=3D 610546.65..610546.75 rows=3D288 loops=3D1) -> Index Scan using lan_pkey on lan (cost=3D0.00..17.12 rows=3D1 width=3D16) (actual time=3D7524.47..610533.50 rows=3D288 loops=3D1) Total runtime: 610565.51 msec EXPLAIN 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 = ; NOTICE: QUERY PLAN: Sort (cost=3D17.13..17.13 rows=3D1 width=3D16) (actual time=3D 1260756.66..1260756.76 rows=3D288 loops=3D1) -> Index Scan using lan_pkey on lan (cost=3D0.00..17.12 rows=3D1 width= =3D16) (actual time=3D7725.97..1260752.47 rows=3D288 loops=3D1) Total runtime: 1260757.09 msec pdb=3D# \d lan Table "lan" Column | Type | Modifiers ------------------+-----------------------+----------- bname | character varying(64) | not null sdate | date | not null stime | integer | not null cno | smallint | not null pno | smallint | not null rbts | bigint | tbts | bigint | u_inpkt | bigint | u_outpkt | bigint | m_inpkt | bigint | m_outpkt | bigint | b_inpkt | bigint | b_outpkt | bigint | Primary key: lan_pkey Check constraints: "lan_stime" ((stime >=3D 0) AND (stime < 86400)) On 4/10/06, Joshua D. Drake wrote: > > 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* < soni.de@gmail.com > > 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 wh= y > > it is slowing down the performance. > > please provide some help regarding improving the performance and ho= w > > do I run query so that records will be fetched in a less time. > > > > > > > -- > > =3D=3D=3D The PostgreSQL Company: Command Prompt, Inc. =3D=3D= =3D > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > > ------=_Part_2509_16979380.1144739127476 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

 

I have flushed the database, so currently r= ecords 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=3D# explain analyze SELECT sdate, stime= , rbts from lan WHERE (

 ( bname =3D 'pluto' ) AND ( cno =3D 1= 7 ) AND ( pno =3D 1 ) AND ( ( sdate

 >=3D '2004-07-21' ) AND ( sdate &l= t;=3D '2004-07-21' ) )  )  ORDER BY sdate, stime ;<= /p>

NOTICE:  QUERY PLAN:

Sort  (cost=3D17.13..17.13 rows=3D1 wi= dth=3D16) (actual time=3D619140.18..619140.29 rows

=3D288 loops=3D1)

  ->  Index Scan using lan_pke= y on lan  (cost=3D0.00..17.12 rows=3D1 width=3D16) (ac

tual time=3D7564.44..619121.61 rows=3D288 l= oops=3D1)

Total runtime: 619140.76 msec=

 

EXPLAIN

 

bsdb=3D# explain analyze SELECT DISTINCT sd= ate, stime, rbts from lan

 WHERE (  ( bname =3D 'pluto' ) A= ND ( cno =3D 17 ) AND ( pno =3D 1 ) AND ( ( sdate >=3D '2004-07-21' ) AN= D ( sdate <=3D '2004-07-21' ) )  ) =20

 ORDER BY sdate, stime ;=

NOTICE:  QUERY PLAN:

 

Unique  (cost=3D17.13..17.14 rows=3D1 = width=3D16) (actual time=3D610546.66..610564.31 rows=3D288 loops=3D1)

  ->  Sort  (cost=3D17.13= ..17.13 rows=3D1 width=3D16) (actual time=3D610546.65..610546.75 rows=3D288= loops=3D1)

        = ->  Index Scan using lan_pkey on lan  (cost=3D0.00..17.12 rows= =3D1 width=3D16) (actual time=3D7524.47..610533.50 rows=3D288 loops=3D1)

Total runtime: 610565.51 msec=

 

EXPLAIN

 

pdb=3D# explain analyze SELECT ALL sdate, s= time, 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 ;

NOTICE:  QUERY PLAN:

 

Sort  (cost=3D17.13..17.13 rows=3D1 wi= dth=3D16) (actual time=3D1260756.66..1260756.76 rows=3D288 loops=3D1)

  ->  Index Scan using lan_pke= y on lan  (cost=3D0.00..17.12 rows=3D1 width=3D16) (actual time=3D7725= .97..1260752.47 rows=3D288 loops=3D1)

Total runtime: 1260757.09 msec

 

 

pdb=3D# \d lan

       &= nbsp;           &nbs= p; Table "lan"

      Column =      |         = Type          | Modifiers

------------------+-----------------------+= -----------

 bname     &n= bsp;      | character varying(64) | not null

 sdate     &n= bsp;      | date     &nbs= p;            | not = null

 stime     &n= bsp;      | integer     &= nbsp;         | not null

 cno     &nbs= p;        | smallint   &n= bsp;          | not null

 pno     &nbs= p;        | smallint   &n= bsp;          | not null

 rbts     &nb= sp;       | bigint    &nb= sp;           |

 tbts     &nb= sp;       | bigint    &nb= sp;           |

 u_inpkt     =      | bigint       =          |

 u_outpkt     = ;    | bigint        = ;        |

 m_inpkt      = ;    | bigint       =          |

 m_outpkt     = ;    | bigint        = ;        |

 b_inpkt     =      | bigint       =          |

 b_outpkt     = ;    | bigint        = ;        |

Primary key: lan_pkey

Check constraints: "lan_stime" ((= stime >=3D 0) AND (stime < 86400))



On 4/10/06, = Joshua D. Drake <jd@commandp= rompt.com> wrote:
Rajesh Kumar Mallah wrote:
&g= t;
> what is the query ?
> use LIMIT or a restricting where cla= use.

You could also use a cursor.

Joshua D. Drake
>
>=
> regds
> mallah.
>
> On 4/10/06, *soni de* < <= a href=3D"mailto:soni.de@gmail.com">soni.de@gmail.com <mailto: soni.de@gmail.com>> wrote:
>
>    = ; Hello,
>
>     I have difficulty in fetch= ing the records from the database.
>     Database= table contains more than 1 GB data.
>     For fe= tching 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 query so that = records will be fetched in a less time.
>
>


--

         &nb= sp;  =3D=3D=3D The PostgreSQL Company: Command Prompt, Inc. =3D= =3D=3D
      Sales/Support: +1.503.667.456= 4 || 24x7/Emergency: +1.800.492.2240
      = ;Providing the most comprehensive  PostgreSQL solutions since 199= 7
            = ;         http://www.commandprompt.com/


=

------=_Part_2509_16979380.1144739127476--