Re: very large db performance question

> Thanks for reply. Actually our database only supply
> some scientists to use (we predict that). so there
> is no workload problem. there is only very
> infrequent updates. the query is not complex. the
> problem is, we have one table that store most of the
> data ( with 200 million rows). In this table, there
> is a text column which we need to do full text
> search for each row. The result will then join the
> data from another table which has 30,000 rows. Now
> the query runs almost forever.

Use TSearch2.

> I tried a small table with 2 million rows using  the
> following simple command, it takes me about 6
> seconds to get the result back. So, I get confused.
> That is why I ask: Is it the hardware problem or
> something else. (I just vacuumed the whole database
> yesterday).
>
> PGA=> select count (*) from expressiondata ;
>   count
> ---------
>  2197497
> (1 row)

select count(*) on a postgres table ALWAYS does a sequential scan.  Just
don't do it.  There are technical reasons (MVCC) why this is so.  It's a
bad "test".

Chris