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

[Mark Cave-Ayland]

On Tue, 2007-06-12 at 21:56 +0200, Christo Du Preez wrote:
> 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


Okay. So the next step is write your query from the ground up, adding in
one join at a time until you find the one that triggers the performance
drop. e.g.


SELECT *
FROM layer l
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))')

then

SELECT *
FROM layer l, theme t
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'


etc.

Keep adding in each join term, one at a time, until you find the point
at which the query slows down. Verify that adding all the other join
clauses afterwards still maintains the performance, and then report back
on your findings.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk