On 8/15/06, Andrew Sullivan <ajs@crankycanuck.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.