Re: Takes too long to fetch the data from database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux