Re: [Gnumed-devel] Praxis encounter location slow to populate

[Karsten Hilbert]

On Fri, Jun 13, 2014 at 12:18:34AM +0200, Karsten Hilbert wrote:

> OK, I was able to load the data into my database.
> 
> I am getting pretty much the same plan. However, for me it
> "only" takes 2.7 seconds to run.
> 
> I would suggest also taking a look at a few PostgreSQL tuning
> sites and applying their advice.
> 
>       
> https://www.google.de/search?ie=ISO-8859-1&hl=de&source=hp&q=postgresql+tuning&btnG=Google-Suche&gbv=1

Without resorting to tuning my PG server (which proved 
unhelpful) I have been able to speed up the praxis branch
phrasewheel significantly by using an array of measures:

        Fix slow praxis branch PRW

        - improved dem.v_praxis_branches/dem.v_orgs/dem.v_org_units
        - dem.v_org_units_no_praxis_check
        - install pg_trgm extension
        - use trigram GIN index to speed up ILIKE/~* matching
        - don't match against unindexable l10n_unit_category view field
        - use UNION rather than OR in praxis branch match provider
        - increase praxis branch PRW picklist delay

The query itself now runs in 156ms rather than 2-4s. Also the
phrasewheel now only starts running the query after a delay
of 300ms (rather than 150ms) which, for me, allows for
tabbing through the field either direction without ever
running the lookup (and thus no delay at all).

The one drawback (for which there's no fix short of using a
materialized view proper support for only comes starting with
PostgreSQL 9.3) is that we now cannot type the organizational
units category for selecting the desired praxis branch anymore.

Example:

        org "LMcCs's praxis" with units:
                lab (enterprise science deck)
                ward (at Starfleet general)
                ER (enterprise sickbay)
                praxis (enterprise offices of LMcC)

We cannot now anymore type

        "prax"

into the praxis branch phrasewheel and have "enterprise
offices of LMcC" show up as a match :-(

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346