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

[Busser, Jim]

On 2014-06-05, at 9:52 AM, Karsten Hilbert <address@hidden wrote:

> 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.

On running the script, my terminal showed the appended.

Should the changes be immediately active?

I ask because the output of the queries looks unimproved … maybe postgres is 
failing to take advantage of the index?

See
        http://explain.depesz.com/s/GXex
        http://explain.depesz.com/s/Vh7

-- Jim


gnumed_v19=> \i ~/Desktop/v19-dem-org-idx-fixup.sql
SET
psql:/Users/djb/Desktop/v19-dem-org-idx-fixup.sql:12: NOTICE:  index 
"idx_dem_org_fk_category" does not exist, skipping
DROP INDEX
CREATE INDEX
psql:/Users/djb/Desktop/v19-dem-org-idx-fixup.sql:16: NOTICE:  index 
"idx_dem_org_unit_fk_category" does not exist, skipping
DROP INDEX
CREATE INDEX
psql:/Users/djb/Desktop/v19-dem-org-idx-fixup.sql:19: NOTICE:  index 
"idx_dem_org_unit_fk_address" does not exist, skipping
DROP INDEX
CREATE INDEX
       log_script_insertion       
----------------------------------
 9765373098b03fb208332498f34cd4b5
(1 row)

======
end