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

[Christo Du Preez]

No one has changed the default statistics, is it purely a  dump/restore
process to get the data on the server once I'm happy.

I've tried what you suggested, and there is no change in the performance
whatsoever.

ALTER TABLE layer ALTER COLUMN layertypeid SET STATISTICS 100;
ANALYZE;

I also ran ANALYZE layer; Just to make sure and VACUUM ANALYZE VERBOSE
layer; just to make dead sure :)

Here is the EXPLAIN ANALYZE as you requested.

EXPLAIN ANALYZE SELECT *
FROM layer l, theme t, visiblelayer v, layertype lt, style s
WHERE l.the_geom && geomfromtext('POLYGON((-90.0 -90.0, -90.0 90.0, 90.0
90.0, 90.0 -90.0, -90.0 -90.0))')
AND t.name = 'default'
AND v.themeid = t.id
AND v.zoomlevel = 1
AND v.enabled
AND l.layertypeid = v.layertypeid
AND lt.id = l.layertypeid
AND s.id = v.styleid
ORDER BY lt.zorder ASC;

 Sort  (cost=292815.29..293579.56 rows=305710 width=602) (actual
time=1645.801..1645.879 rows=184 loops=1)
   Sort Key: lt.zorder
   ->  Hash Join  (cost=31.51..52688.35 rows=305710 width=602) (actual
time=475.746..1644.993 rows=184 loops=1)
         Hash Cond: (l.layertypeid = v.layertypeid)
         ->  Seq Scan on layer l  (cost=0.00..43271.41 rows=562518
width=284) (actual time=0.070..1332.116 rows=565263 loops=1)
               Filter: (the_geom &&
'0103000000010000000500000000000000008056C000000000008056C000000000008056C0000000000080564000000000008056400000000000805640000000000080564000000000008056C000000000008056C000000000008056C0'::geometry)
         ->  Hash  (cost=31.42..31.42 rows=7 width=318) (actual
time=1.111..1.111 rows=3 loops=1)
               ->  Hash Join  (cost=3.90..31.42 rows=7 width=318)
(actual time=0.128..1.095 rows=3 loops=1)
                     Hash Cond: (v.styleid = s.id)
                     ->  Nested Loop  (cost=2.74..30.17 rows=7
width=154) (actual time=0.097..1.057 rows=3 loops=1)
                           Join Filter: (v.themeid = t.id)
                           ->  Seq Scan on theme t  (cost=0.00..1.01
rows=1 width=23) (actual time=0.005..0.006 rows=1 loops=1)
                                 Filter: (name = 'default'::text)
                           ->  Hash Join  (cost=2.74..29.07 rows=7
width=131) (actual time=0.087..1.041 rows=3 loops=1)
                                 Hash Cond: (lt.id = v.layertypeid)
                                 ->  Seq Scan on layertype lt 
(cost=0.00..18.71 rows=671 width=110) (actual time=0.015..0.510 rows=671
loops=1)
                                 ->  Hash  (cost=2.65..2.65 rows=7
width=21) (actual time=0.061..0.061 rows=3 loops=1)
                                       ->  Seq Scan on visiblelayer v 
(cost=0.00..2.65 rows=7 width=21) (actual time=0.027..0.055 rows=3 loops=1)
                                             Filter: ((zoomlevel = 1)
AND enabled)
                     ->  Hash  (cost=1.07..1.07 rows=7 width=164)
(actual time=0.022..0.022 rows=7 loops=1)
                           ->  Seq Scan on style s  (cost=0.00..1.07
rows=7 width=164) (actual time=0.007..0.012 rows=7 loops=1)
 Total runtime: 1646.145 ms




Mark Cave-Ayland wrote:
> 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.
>
>   

-- 
Christo Du Preez

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