[Phpgedview-talk] Hello and suggested optimization

[Shane H. <sh...@ha...>]

Hello,

By way of introduction, I'm a software engineer working on genealogy
software at the LDS church.  I wrote a lot of the functionality (and
perhaps bugs too) of Scan Stone, a high speed microfilm scanning system
that was recently featured in the Church News.  I'm also a Zope expert
and one of the founders of the Content Management Framework (CMF), the
technology behind Plone, a major open source content management system.

I just installed PGV 4.0 beta 8 and uploaded a GEDCOM with 5,000 names.
 It worked well and I can see an impressive list of features.  I've been
interested in the project for a long time.

I used PostgreSQL 8.0 as the backend.  I noticed that the welcome page
takes a long time to load, so I did a little investigating.  I set
"log_min_duration_statement = 100" in postgresql.conf, which causes
PostgreSQL to log any query that takes longer than 100 ms.  Then,
postgresql.log revealed two queries that were each taking 33 seconds on
every visit to the welcome page.  I used EXPLAIN ANALYZE to find out why
it was taking so long, and it became apparent that it didn't have the
right indexes to perform this query.

I tried various combinations of indexes until I settled on the following
simple index, which reduces the 33 second queries to 20 ms:

  create index date_death_opt on pgv_dates (d_fact, d_gid);

This is a whopping 1,650 times (165000%) faster. :-)

Just thought you'd like a simple optimization.  I haven't read a single
line of code of PGV yet, so I don't know where the optimization should go.

Shane