Re: [Phpgedview-talk] Hello and suggested optimization

[John F. <Joh...@ne...>]

Hi Shane,

Thanks for looking into these optimizations.

Those indexes should already be created when your database is setup.
See the create_dates_table() function (about line 1078) in
functions_import.php.  Unless postgresql treats the two fields indexed
together differently then the two indexed seperately.

I did some testing on this and I wasn't able to duplicate your results.
But I might not have the same configuration as you.  Can you post the
SQL queries that were taking so long to run?  What blocks do you have
configured on your welcome page?

Thanks,
--John

-----Original Message-----
From: php...@li...
[mailto:php...@li...] On Behalf Of
Shane Hathaway
Sent: Sunday, June 04, 2006 11:15 PM
To: php...@li...
Subject: [Phpgedview-talk] Hello and suggested optimization

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 =3D 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



_______________________________________________
Phpgedview-talk mailing list
Php...@li...
https://lists.sourceforge.net/lists/listinfo/phpgedview-talk