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.29 rows
=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))
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@xxxxxxxxx <mailto: soni.de@xxxxxxxxx>> 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/
