[postgis-users] test / live environment, major performance difference

[Christo Du Preez]

Hi Mark,

My installation is:

PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20061115 (prerelease) (SUSE Linux)
POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct
2006" USE_STATS

The postgresql.conf is confirmed to be identical and still experiencing the same issue. We also vacuumed the tables, using full, analyze and freeze, and reindex just to make sure. All the indexes are present, same as on the fast machine.

Only about 5 layertypeid's are used in the layer and visiblelayer table but we have 670 different layertypes in the layertype table.

Thanx for all the help.

 


Mark Cave-Ayland wrote:
> On Tue, 2007-06-12 at 08:37 +0200, Christo Du Preez wrote:
>   
>> I wonder if my dump/restore routine isn't causing this issue. Seeing
>> that I do the db development on my laptop (the fast one) and then
>> restores it on the other two machines. I have confirmed if all the
>> indexes are present after a restore.
>>
>> This is the routine:
>>
>> /usr/local/pgsql/bin/pg_dump -t layer mapdb | gzip > layer.gz
>>
>> rsync --progress --rsh=ssh layer.gz
>> root@???.???.???.???:/home/postgres/layer.gz
>>
>> --
>>
>> /usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz
>>
>> rsync --progress --rsh=ssh visiblelayer.gz
>> root@???.???.???.???:/home/postgres/visiblelayer.gz
>>
>> --
>>
>> /usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz
>>
>> rsync --progress --rsh=ssh style.gz
>> root@???.???.???.???:/home/postgres/style.gz
>>
>> --
>>
>> /usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz
>>
>> rsync --progress --rsh=ssh layertype.gz
>> root@???.???.???.???:/home/postgres/layertype.gz
>>
>> --
>>
>> DROP TABLE visiblelayer;
>> DROP TABLE style;
>> DROP TABLE layer;
>> DROP TABLE layertype;
>>
>> gunzip -c layertype.gz | /usr/local/pgsql/bin/psql mapdb
>> gunzip -c style.gz | /usr/local/pgsql/bin/psql mapdb
>> gunzip -c visiblelayer.gz | /usr/local/pgsql/bin/psql mapdb
>> gunzip -c layer.gz | /usr/local/pgsql/bin/psql mapdb
>>
>> /usr/local/pgsql/bin/vacuumdb -d mapdb -z -v
>>     
>
>
> Hi Christo,
>
> Are you sure that your postgresql.conf files are identical for both
> installations? (you also haven't mentioned which versions of
> PostGIS/PostgreSQL you are using). Looking quickly at the plans, on your
> development (fast) server, the planner estimates that an index scan
> joining l.layertypeid onto v.layertypeid should return 88,000 rows when
> in fact it returns only 60. How many different layertypeids do you have?
>
>
> Kind regards,
>
> Mark.
>
>   

-- 
Christo Du Preez

Senior Software Engineer
Mecola IT
Mobile:	 +27 [0]83 326 8087
Skype:	 christodupreez
Website: http://www.locateandtrade.co.za