Re: [Phpgedview-talk] Hello and suggested optimization

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

Those queries are for the statistics block.  I reinstalled Postgres and
I was able to duplicate the results you had.

For the next release, I will make sure that that index is created.  Most
users use MySQL so if you find any other slow queries in pgsql please
let me know.

Thanks,
--John


-----Original Message-----
From: php...@li...
[mailto:php...@li...] On Behalf Of
Shane Hathaway
Sent: Wednesday, June 07, 2006 12:57 AM
To: All About PhpGedView
Subject: Re: [Phpgedview-talk] Hello and suggested optimization

John Finlay wrote:
> 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.

Yes, it treats them very differently, as does MySQL and any relational
database.  Computing a result based on multiple independent indexes is
typically not an efficient operation.  Queries that look up a row based
on the values in multiple columns are often much faster if they use an
index that combines all of the required columns.

Imagine having a database table of the geospatial coordinates of every
house on earth.  One column would have the longitude and another would
have the latitude.  Create one index for the longitude column and
another for the latitude column.  If you want to look up a house at a
particular latitude and longitude, the longitude index can quickly tell
you about the thousands (millions?) of houses along a slice of the
earth, and the latitude index can quickly tell you about the houses
along a different slice, but no index can tell you about the houses at
particular coordinates.  You have to merge the lists of houses from the
two indexes, or fall back to scanning the table.

If you instead create an index that combines the longitude and the
latitude, you can look up the house directly, without merging anything.
 It's as if the table were pre-sorted by both the latitude and
longitude.  The only problem is that the index may consume a lot of disk
space, so it's a trade-off.

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

It's a default install.  I am a newbie to PGV.  This is the first I've
heard that you can actually configure the welcome page. ;-)

The offending queries are below.  Each originally took about 33000 ms,
dropping to 20 ms with a more appropriate index.  (Interestingly, it
only took 130 ms to "create index"!)

select avg(death.d_year-birth.d_year) as age from pgv_dates as death,
pgv_dates as birth where birth.d_gid=3Ddeath.d_gid AND =
death.d_file=3D'1'
and birth.d_file=3Ddeath.d_file AND birth.d_fact=3D'BIRT' and
death.d_fact=3D'DEAT' AND birth.d_year>0 and death.d_year>0 and
birth.d_type is null and death.d_type is null;

select death.d_year-birth.d_year as age, death.d_gid from pgv_dates as
death, pgv_dates as birth where birth.d_gid=3Ddeath.d_gid AND
death.d_file=3D'1' and birth.d_file=3Ddeath.d_file AND =
birth.d_fact=3D'BIRT'
and death.d_fact=3D'DEAT' AND birth.d_year>0 and death.d_year>0 and
birth.d_type is null and death.d_type is null ORDER BY age DESC LIMIT 1
OFFSET 0;

Shane



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