Re: [Gnumed-devel] Welcome Back

[Richard Terry]

Oh, Hi Karsten,

I havn't touched the keyboard since our last communication. Not a byte. Am 
attending spanish lessons each Monday (maybe Carlos could correspond with 
me), and am struggling with that as I"ve no head for languages.

Will try and put back on my computing cap.


Richard

On Saturday 18 March 2006 02:32, Karsten Hilbert wrote:
> 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