[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.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346