On Tue, 2007-06-12 at 11:44 +0200, Christo Du Preez wrote: > 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.
Hi Christo, Has anyone changed the default statistics values for your columns using ALTER TABLE x ALTER COLUMN y SET STATISTICS z? For example, your fast query is still badly estimating your index scan using the fki_layer_layertypeid as returning 88,000 rows when in fact it only returns 60. My current guess is that your production machines are overestimating this join further which makes the sequential scan seem the cheapest option. I'd be inclined to try increasing the statistics on your layertypeid column on your layer table, e.g. ALTER TABLE layers ALTER COLUMN layertypeid SET STATISTICS 100; ANALYZE; If that doesn't solve the problem, then the next step I'd try is to nudge the value of random_page_cost down from 4 to 3 in postgresql.conf. And if neither of these work, can you post the new EXPLAIN ANALYZE of the query with these new settings in place? Kind regards, Mark. -- ILande - Open Source Consultancy http://www.ilande.co.uk _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
