Date: Tue, 15 Aug 2006 10:17:12 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Multiple DB join
Message-ID: <20060815141712.GB21939@phlogiston.dyndns.org>
References: <44E0EA72.2090505@indiana.edu>
	<20060815103624.GA21634@phlogiston.dyndns.org>
	<44E1D61D.4020400@indiana.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <44E1D61D.4020400@indiana.edu>
User-Agent: Mutt/1.5.9i

On Tue, Aug 15, 2006 at 10:11:41AM -0400, Sumeet Ambre wrote:
> >  
> 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. 

It's this "different instances" thing that I'm having a tough time
with.  Is this because they want to be able to query local things
when disconnected or something?  I can think of applications for
this, for sure, I'm just suggesting that you make sure you're not
building an optimisation that is (1) premature and (2) possibly a
pessimal operation.

> 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......

The problem is not your design, nor even the size of the data
exactly, but the query: 

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

You have two initially-unbound search terms there: ILIKE '%' and ~*
'.*' are automatically seqscans, because you have nowhere in the
index to start.  If you really want to do this kind of unbound-string
query, you need to look into full text search.  The above approach is
never going to be fast.

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie