soni de wrote:
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) (actual time=7564.44..619121.61 rows=288 loops=1) Total runtime: 619140.76 msec
OK - there is clearly something wrong here when you take 10 minutes to fetch 288 rows from an index.
1. VACUUM FULL VERBOSE lan; 2. test again, and if that doesn't work... 3. REINDEX TABLE lan; 4. test again I'm guessing you have a *lot* of dead rows in there. -- Richard Huxton Archonet Ltd
