pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: LIANHE SHAO <lshao2@jhmi.edu>
Cc: Neil Conway <neilc@samurai.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: very large db performance question
Date: Thu, 27 Nov 2003 09:03:14 +0800
Message-ID: <3FC54D52.4040207@familyhealth.com.au> (raw)
In-Reply-To: <4e2c4d4e061f.4e061f4e2c4d@jhmimail.jhmi.edu>
References: <4e2c4d4e061f.4e061f4e2c4d@jhmimail.jhmi.edu>

> 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





view thread (6+ messages)  latest in thread

Message-ID: <3FC54D52.4040207@familyhealth.com.au>
Permalink:  ../3FC54D52.4040207@familyhealth.com.au/
Also on:    postgresql.org/message-id/3FC54D52.4040207@familyhealth.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: chriskl@familyhealth.com.au, lshao2@jhmi.edu, neilc@samurai.com
  Subject: Re: very large db performance question
  In-Reply-To: <3FC54D52.4040207@familyhealth.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox