Re: 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 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(at)commandprompt(dot)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(dot)de(at)gmail(dot)com <mailto:soni(dot)de(at)gmail(dot)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/
>
>
>