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

[Obe, Regina]

Just curious what happens when you do this

SET enable_seqscan = off

Then run your query again with analyze.

SET enable_seqscan = on

Then run an query again with analyze

What does your plan look like on the servers having the issue in both
cases.  Maybe that will give you a clue what is going on why its not
using the indexes.

Hope that helps,
Regina 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Christo Du Preez
Sent: Tuesday, June 12, 2007 5:44 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] test / live environment,major performance
difference

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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.