From: soni de <soni.de@gmail.com>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Takes too long to fetch the data from database
Date: Tue, 11 Apr 2006 12:35:27 +0530
Message-ID: <9f2e40a90604110005o101fe98enec2f8ac8f757c770@mail.gmail.com> (raw)
In-Reply-To: <443AA1D4.9010308@commandprompt.com>
References: <9f2e40a90604100021n1cb694d0lb95f318f800f33ee@mail.gmail.com>
<a97c77030604100858h4ef89e92sa6ecdd0aca735edb@mail.gmail.com>
<443AA1D4.9010308@commandprompt.com>
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 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
;
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)
(ac
tual time=7564.44..619121.61 rows=288 loops=1)
Total runtime: 619140.76 msec
EXPLAIN
bsdb=# explain analyze SELECT DISTINCT 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 ;
NOTICE: QUERY PLAN:
Unique (cost=17.13..17.14 rows=1 width=16) (actual time=
610546.66..610564.31 rows=288 loops=1)
-> Sort (cost=17.13..17.13 rows=1 width=16) (actual time=
610546.65..610546.75 rows=288 loops=1)
-> Index Scan using lan_pkey on lan (cost=0.00..17.12 rows=1
width=16) (actual time=7524.47..610533.50 rows=288 loops=1)
Total runtime: 610565.51 msec
EXPLAIN
pdb=# explain analyze SELECT ALL sdate, stime, rbts from lan WHERE ( (
bname = 'neptune' ) AND ( cno = 17 ) AND ( pno = 1 ) AND ( ( sdate >=
'2004-07-21' ) AND ( sdate <= '2004-07-21' ) ) ) ORDER BY sdate, stime ;
NOTICE: QUERY PLAN:
Sort (cost=17.13..17.13 rows=1 width=16) (actual time=
1260756.66..1260756.76 rows=288 loops=1)
-> Index Scan using lan_pkey on lan (cost=0.00..17.12 rows=1 width=16)
(actual time=7725.97..1260752.47 rows=288 loops=1)
Total runtime: 1260757.09 msec
pdb=# \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 >= 0) AND (stime < 86400))
On 4/10/06, Joshua D. Drake <jd@commandprompt.com> 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 <mailto: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 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.
> >
> >
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: soni.de@gmail.com, jd@commandprompt.com, mallah.rajesh@gmail.com
Subject: Re: Takes too long to fetch the data from database
In-Reply-To: <9f2e40a90604110005o101fe98enec2f8ac8f757c770@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox