[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Gnumed-devel] optimizations

From: Karsten Hilbert
Subject: Re: [Gnumed-devel] optimizations
Date: Fri, 17 Mar 2006 16:32:25 +0100
User-agent: Mutt/1.5.11+cvs20060126

On Thu, Mar 09, 2006 at 10:25:23PM +0800, Syan Tan wrote:

> experimenting with a large dataset, I found that there are some problems with
> the postgresql query planner which requires some manipulation of the sql to
> compensate for.
> For instance, a 1-2 minute access of a fairly large record becomes 4 seconds .
> the problem is selecting on a base table where there exists child tables with
> large dataset. e.g. clin_root_item and clin_narrative ( with 15000+) entries.
> indexes exist for the search condition piece in both base table and child 
> table
> , but the default for the qeury parser is to sequentially search the 15000 
> entries of
> the child table without using the index.
> the optimization is to explicitly search each child table and join , and then
> get the union of the joins. This reduces a 10000 msec search time to about 1 
> msec.

Syan, let's solve this slightly differently: Write a view
"clin.v_pat_items_union" which does the equivalent of
"clin.v_pat_items" but uses explicit unions. Let's then
modify the middleware query to select from that instead of
v_pat_items and make a comment on why. That way we will have
the fix with the minimal impact on how we want things to
*actually* be.

GPG key ID E4071346 @
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

reply via email to

[Prev in Thread] Current Thread [Next in Thread]