X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id F2E9F9FB2F9 for ; Tue, 15 Aug 2006 11:35:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 64855-05 for ; Tue, 15 Aug 2006 14:35:40 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 9BBED9FB2DB for ; Tue, 15 Aug 2006 11:35:39 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so392188ugc for ; Tue, 15 Aug 2006 07:35:36 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=o7MZ3G3Go3nHUtPHjB0nMS/4L/35XXsiXrAvmnLSgwP+9e6ONn0Z1KBAC28KxJe/ZDiRLV+L+0LLWGbzP/6g+O3FvwVyI5+OigsIRO2kUKgdKO5XxPkn8mTBCKuPM5k5Nj1xycTTCRSoNSNdn1ioDoyq9T9UEz0t69Yj8GyJLU8= Received: by 10.67.119.5 with SMTP id w5mr242402ugm; Tue, 15 Aug 2006 07:35:36 -0700 (PDT) Received: by 10.66.248.13 with HTTP; Tue, 15 Aug 2006 07:35:36 -0700 (PDT) Message-ID: <7539aebb0608150735l34e59630m25487549c8e6de2d@mail.gmail.com> Date: Tue, 15 Aug 2006 10:35:36 -0400 From: Sumeet To: "Andrew Sullivan" Subject: Re: Multiple DB join Cc: pgsql-sql@postgresql.org In-Reply-To: <20060815141712.GB21939@phlogiston.dyndns.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_169949_5208552.1155652536445" References: <44E0EA72.2090505@indiana.edu> <20060815103624.GA21634@phlogiston.dyndns.org> <44E1D61D.4020400@indiana.edu> <20060815141712.GB21939@phlogiston.dyndns.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.2 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/112 X-Sequence-Number: 26070 ------=_Part_169949_5208552.1155652536445 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/15/06, Andrew Sullivan 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. ------=_Part_169949_5208552.1155652536445 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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.
------=_Part_169949_5208552.1155652536445--