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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
