On Thu, Apr 3, 2008 at 2:34 PM, Dan99 <power919@xxxxxxxxx> wrote: > Hi, > > I am having some troubles with a select group of tables in a database > which are acting unacceptably slow. For example a table with > approximately < 10,000 rows took about 3,500ms to extract a single row > using the following select statement: > > SELECT * FROM table WHERE column = 'value' > > I have preformed this same test on a number of different tables, only > a few of which have this same problem. The only common thing that I > can see between these affected tables is the fact that they are dumped > and re-populated every day from an outside source. You need to ANALYZE the tables after you load them, and make sure you have indexes on the column you're querying (which it sounds like you do, but they're not being used because the statistics for the table are inaccurate). There may also be a lot of dead tuples which will further slow down a sequential scan. Do read up on VACUUM and MVCC in the docs--it's a very important thing. You will suffer horribly unless you have a working periodic VACUUM. Also, are you using TRUNCATE TABLE to clear out before the reload, or a mass DELETE? The latter will leave a lot of dead rows, bloating the table and slowing down scans. TRUNCATE just deletes the table file and recreates it empty. -Doug
