From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Multiple DB join
Date: Tue, 15 Aug 2006 10:17:12 -0400
Message-ID: <20060815141712.GB21939@phlogiston.dyndns.org> (raw)
In-Reply-To: <44E1D61D.4020400@indiana.edu>
References: <44E0EA72.2090505@indiana.edu>
<20060815103624.GA21634@phlogiston.dyndns.org>
<44E1D61D.4020400@indiana.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: Multiple DB join
In-Reply-To: <20060815141712.GB21939@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox