Re: [Gnumed-devel] optimizations

[Karsten Hilbert]

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.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346