Message-ID: <44E1D61D.4020400@indiana.edu>
Date: Tue, 15 Aug 2006 10:11:41 -0400
From: Sumeet Ambre <sambre@indiana.edu>
User-Agent: Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
To: Andrew Sullivan <ajs@crankycanuck.ca>
CC: pgsql-sql@postgresql.org
Subject: Re: Multiple DB join
References: <44E0EA72.2090505@indiana.edu>
	<20060815103624.GA21634@phlogiston.dyndns.org>
In-Reply-To: <20060815103624.GA21634@phlogiston.dyndns.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andrew Sullivan wrote:
> On Mon, Aug 14, 2006 at 05:26:10PM -0400, Sumeet Ambre wrote:
>   
>> Hi All,
>>
>> I have a database which consists of 20 million records and I've split up 
>> the db into 6-7 dbs. 
>>     
>
> You can do this (as someone suggested with dblink), but I'm wondering
> why the split?  20 million records isn't very big.
>
> A
>   
The Design of the database is because our organization wants to split up 
different datasets into different entities, and there might be a 
possibility that they'll run different instances of postgres for each 
dataset. The problem is that one of the databases consists of 14 million 
records and when i query the base database which consists of 20million 
records, the query runs damn slow.......below is the sample schema for 
my base table and the query i try to run on it....it takes me more than 
2-3 minutes to run a query....Is there any way i could speed this up......

sample schema for my base table
---------------------------------

doc_id  |  title |  authors  |  pub_year  |   abs   |   db_name........ |

In the above scheme the field db_name is the name of the other databases 
which contain the whole record.
I'm trying to run query which searches on title, authors name, pub_year 
or abstract simultaneously...

e.g.  SELECT * FROM base WHERE ((title ILIKE '%something%') AND (authors 
~*   '.*something.*')  AND (db_name='something'));

Any suggestions to speed up this query.....

Thanks,
Sumeet.