[postgis-users] test / live environment, major performance difference
Obe, Regina
robe.dnd at cityofboston.gov
Tue Jun 12 13:34:10 PDT 2007
Christo,
Did you also try the
set enable_seqscan off;
And then run the query again and look at the plan. No matter how badly
your stats config is, I think that setting should force using any index
possible no matter how badly an optimization that would be. At least
that is my understanding.
If it still gives you the same plan, then something else is wrong like
its not seeing the indexes or possibly you have enable_indexscan set to
off.
What do the below statements give you
show enable_indexscan;
show enable_seqscan;
In a standard state, both should return on.
Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Christo Du Preez
Sent: Tuesday, June 12, 2007 3:57 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] test / live environment,major performance
difference
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 &&
'0103000000010000000500000000000000008056C000000000008056C00000000000805
6C0000000000080564000000000008056400000000000805640000000000080564000000
000008056C000000000008056C000000000008056C0'::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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
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.
More information about the postgis-users
mailing list