Re: [Gnumed-devel] get_encounter speedup

[Karsten Hilbert]

On Fri, May 05, 2006 at 10:30:23PM +0800, Syan Tan wrote:

> I've updated my client code, hope it is fully upto date. the child_tables in
> gmPG makes sense, as this is a general problem.
Yep, and even using the generalized solution for just this
one case relieves us from having to update the middleware
when adding more child tables to clin_root_item :-)   Plus,
the way it's done now, the child tables are only calculated
onces per running instance of GNUmed and only when there
ever is an instance of cClinicalRecord instantiated. So, no
adding of clin_root_item children on the fly, though ;-)

> there seems to be some delays in
> select * from v_pat_narrative, the same problem being sequential scan of child
> tables.
Because we need to go via v_pat_items to get at issue and
patient PKs. Once that "issue is fixed" in the backend it
will automagically fix all users thereof.

> and a select with the condition "age(last_affirmed) < %s seconds" is a lot
> slower than "last_affirmed > now() - %s seconds "
> because I think the later uses an existing index for last_affirmed, whereas 
> age
> (last_affirmed) is  recalculated sequentially on
> all rows for each call.
Precisely, unless one had a functional index on
age(last_affirmed) which we don't. Having one would a)
increase the cost on *insertion* to clin.encounter (which
just might be affordable though) but would b) limit
usefulness of the index to age(last_affirmed) queries while
the index on last_affirmed by itself (as we have now) allows
more queries to be indexed (some may need to be tuned as you
suggest to allow taking the index into account).

I have tweaked all occurrences of age() I could find
according to your suggestion.

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