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

[Karsten Hilbert]

On Thu, Jun 05, 2014 at 04:33:46PM +0000, Jim Busser wrote:

> In both cases, the website tab "Stats" shows a ""Hash Left Join" as taking 
> 3.9 seconds:
> 
>       http://explain.depesz.com/s/FZHs
>       http://explain.depesz.com/s/arCC

Indeed. The probable reason for that is the Sequential
(rather than Index) Scan PostgreSQL is running against
dem.org and dem.org_unit. Looking at the "HTML" tab the hash
condition is listed to include .fk_category -- for which we
had been lacking an index. I have, as written earlier, now
added those indices.

Attached find a file you can run against your v19 database in
order to manually add those indices. The file will be part of
the next bugfix release. Given the new indices PostgreSQL is
likely to pick a better query execution plan.

Please re-run the last queries I sent and post them to
explain.depesz.com so we can be sure it made an improvement.

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

v19-dem-org-idx-fixup.sql
Description: application/sql