Re: Multiple DB join

On 8/15/06, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
>
> 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.

The reason for splitting up the dbs into differnet instances is that in case
one of the postgres instances on the server
goes down for some reason, it doesnt effect the other instances which are
running on the same server. Even I dont know
the reason for this kind of approach. But i've to deal with it.

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

I previously thought of doing the full text search indexing thing...but i
had a intution that the full text search thing is for fields which have very
large strings.......but in my case the strings are not above 200 chars in
length.....so would the full text search thing give me the performance which
we need...also i tried doing combined indexes on title, authors in my base
table......would indexes of any kind help me in this case..

Thanks for ur prompt replies,
Sumeet.

--
Thanks,
Sumeet.