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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 >> [EMAIL PROTECTED]:/home/postgres/layer.gz >> >> -- >> >> /usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz >> >> rsync --progress --rsh=ssh visiblelayer.gz >> [EMAIL PROTECTED]:/home/postgres/visiblelayer.gz >> >> -- >> >> /usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz >> >> rsync --progress --rsh=ssh style.gz >> [EMAIL PROTECTED]:/home/postgres/style.gz >> >> -- >> >> /usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz >> >> rsync --progress --rsh=ssh layertype.gz >> [EMAIL PROTECTED]:/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 [email protected] 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
