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

[Mark Cave-Ayland]

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