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 _______________________________________________ Gnumed-devel mailing list [email protected] http://lists.gnu.org/mailman/listinfo/gnumed-devel
