Hi All,

I really hope someone can shed some light on my problem. I'm not sure if
this is a posgres or potgis issue.

Anyway, we have 2 development laptops and one live server, somehow I
managed to get the same query to perform very well om my laptop, but on
both the server and the other laptop it's really performing bad.

All three environments are running the same versions of everything, the
two laptops are identical and the server is a monster compared to the
laptops.

I have narrowed down the problem (I think) and it's the query planner
using different plans and I haven't got a clue why. Can anyone please
shed some light on this?

EXPLAIN ANALYZE
SELECT l.*
FROM layer l, theme t, visiblelayer v, layertype lt, style s
WHERE l.the_geom && geomfromtext('POLYGON((-83.0 -90.0, -83.0 90.0, 97.0
90.0, 97.0 -90.0, -83.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=181399.77..182144.30 rows=297812 width=370) (actual
time=1384.976..1385.072 rows=180 loops=1)
Sort Key: lt.zorder
-> Hash Join (cost=31.51..52528.64 rows=297812 width=370) (actual
time=398.656..1384.574 rows=180 loops=1)
Hash Cond: (l.layertypeid = v.layertypeid)
-> Seq Scan on layer l (cost=0.00..43323.41 rows=550720
width=366) (actual time=0.016..1089.049 rows=540490 loops=1)
Filter: (the_geom &&
'010300000001000000050000000000000000C054C00000000 0008056C00000000000C054C00000000000805640000000000 04058400000000000805640000000000040584000000000008 056C00000000000C054C000000000008056C0'::geometry)
-> Hash (cost=31.42..31.42 rows=7 width=12) (actual
time=1.041..1.041 rows=3 loops=1)
-> Hash Join (cost=3.90..31.42 rows=7 width=12) (actual
time=0.107..1.036 rows=3 loops=1)
Hash Cond: (v.styleid = s.id)
-> Nested Loop (cost=2.74..30.17 rows=7 width=16)
(actual time=0.080..1.002 rows=3 loops=1)
Join Filter: (v.themeid = t.id)
-> Seq Scan on theme t (cost=0.00..1.01
rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Filter: (name = 'default'::text)
-> Hash Join (cost=2.74..29.07 rows=7
width=20) (actual time=0.071..0.988 rows=3 loops=1)
Hash Cond: (lt.id = v.layertypeid)
-> Seq Scan on layertype lt
(cost=0.00..18.71 rows=671 width=8) (actual time=0.007..0.473 rows=671
loops=1)
-> Hash (cost=2.65..2.65 rows=7
width=12) (actual time=0.053..0.053 rows=3 loops=1)
-> Seq Scan on visiblelayer v
(cost=0.00..2.65 rows=7 width=12) (actual time=0.022..0.047 rows=3 loops=1)
Filter: ((zoomlevel = 1)
AND enabled)
-> Hash (cost=1.07..1.07 rows=7 width=4) (actual
time=0.020..0.020 rows=7 loops=1)
-> Seq Scan on style s (cost=0.00..1.07
rows=7 width=4) (actual time=0.005..0.012 rows=7 loops=1)
Total runtime: 1385.313 ms

----------------------------------

Sort (cost=37993.10..37994.11 rows=403 width=266) (actual
time=32.053..32.451 rows=180 loops=1)
Sort Key: lt.zorder
-> Nested Loop (cost=0.00..37975.66 rows=403 width=266) (actual
time=0.130..31.254 rows=180 loops=1)
-> Nested Loop (cost=0.00..30.28 rows=1 width=12) (actual
time=0.105..0.873 rows=3 loops=1)
-> Nested Loop (cost=0.00..23.14 rows=1 width=4)
(actual time=0.086..0.794 rows=3 loops=1)
-> Nested Loop (cost=0.00..11.14 rows=2 width=8)
(actual time=0.067..0.718 rows=3 loops=1)
Join Filter: (s.id = v.styleid)
-> Seq Scan on style s (cost=0.00..2.02
rows=2 width=4) (actual time=0.018..0.048 rows=7 loops=1)
-> Seq Scan on visiblelayer v
(cost=0.00..4.47 rows=7 width=12) (actual time=0.031..0.079 rows=3 loops=7)
Filter: ((zoomlevel = 1) AND enabled)
-> Index Scan using theme_id_pkey on theme t
(cost=0.00..5.98 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=3)
Index Cond: (v.themeid = t.id)
Filter: (name = 'default'::text)
-> Index Scan using layertype_id_pkey on layertype lt
(cost=0.00..7.12 rows=1 width=8) (actual time=0.010..0.014 rows=1 loops=3)
Index Cond: (lt.id = v.layertypeid)
-> Index Scan using fki_layer_layertypeid on layer l
(cost=0.00..36843.10 rows=88183 width=262) (actual time=0.031..9.825
rows=60 loops=3)
Index Cond: (l.layertypeid = v.layertypeid)
Filter: (the_geom &&
'010300000001000000050000000000000000C054C00000000 0008056C00000000000C054C00000000000805640000000000 04058400000000000805640000000000040584000000000008 056C00000000000C054C000000000008056C0'::geometry)
Total runtime: 33.107 ms

----------------------------------

Thanx in advance.
Christo Du Preez



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 2007-06-11 Christo Du Preez wrote:
> I really hope someone can shed some light on my problem. I'm not sure
> if this is a posgres or potgis issue.
>
> Anyway, we have 2 development laptops and one live server, somehow I
> managed to get the same query to perform very well om my laptop, but
> on both the server and the other laptop it's really performing bad.

You write that you have 3 systems, but provided only two EXPLAIN ANALYZE
results. I will assume that the latter is from your laptop while the
former is from one of the badly performing systems.

> All three environments are running the same versions of everything,
> the two laptops are identical and the server is a monster compared to
> the laptops.

Please provide information what exactly those "same versions of
everything" are. What's the PostgreSQL configuration on each system? Do
all three systems have the same configuration? Information on the
hardware wouldn't hurt either.

[...]
> Sort (cost=181399.77..182144.30 rows=297812 width=370) (actual
> time=1384.976..1385.072 rows=180 loops=1)
[...]
> Sort (cost=37993.10..37994.11 rows=403 width=266) (actual
> time=32.053..32.451 rows=180 loops=1)

The row estimate of the former plan is way off (297812 estimated <-> 180
actual). Did you analyze the table recently? Maybe you need to increase
the statistics target.

Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> -----Original Message-----
> From: Christo Du Preez
> Sent: Monday, June 11, 2007 10:10 AM
>
> I have narrowed down the problem (I think) and it's the query
> planner using different plans and I haven't got a clue why.
> Can anyone please shed some light on this?

Different plans can be caused by several different things like different
server versions, different planner settings in the config file, different
schemas, or different statistics. You say the server versions are the same,
so that's not it. Is the schema the same? One isn't missing indexes that
the other has? Do they both have the same data, or at least very close to
the same data? Have you run analyze on both of them to update their
statistics? Do they have the same planner settings in the config file? I
would check that stuff out and see if it helps.

Dave


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On 2007-06-11 Christo Du Preez wrote:
> I really hope someone can shed some light on my problem. I'm not sure
> if this is a posgres or potgis issue.
>
> Anyway, we have 2 development laptops and one live server, somehow I
> managed to get the same query to perform very well om my laptop, but
> on both the server and the other laptop it's really performing bad.

One simple possibility that bit me in the past: If you do pg_dump/pg_restore to create a copy of the database, you have to ANALYZE the newly-restored database. I mistakenly assumed that pg_restore would do this, but you have to run ANALYZE explicitely after a restore.

Craig


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I wonder if my dump/restore routine isn't causing this issue. Seeing
that I do the db development on my laptop (the fast one) and then
restores it on the other two machines. I have confirmed if all the
indexes are present after a restore.

This is the routine:

/usr/local/pgsql/bin/pg_dump -t layer mapdb | gzip > layer.gz

rsync --progress --rsh=ssh layer.gz
root@???.???.???.???:/home/postgres/layer.gz

--

/usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz

rsync --progress --rsh=ssh visiblelayer.gz
root@???.???.???.???:/home/postgres/visiblelayer.gz

--

/usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz

rsync --progress --rsh=ssh style.gz
root@???.???.???.???:/home/postgres/style.gz

--

/usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz

rsync --progress --rsh=ssh layertype.gz
root@???.???.???.???:/home/postgres/layertype.gz

--

DROP TABLE visiblelayer;
DROP TABLE style;
DROP TABLE layer;
DROP TABLE layertype;

gunzip -c layertype.gz | /usr/local/pgsql/bin/psql mapdb
gunzip -c style.gz | /usr/local/pgsql/bin/psql mapdb
gunzip -c visiblelayer.gz | /usr/local/pgsql/bin/psql mapdb
gunzip -c layer.gz | /usr/local/pgsql/bin/psql mapdb

/usr/local/pgsql/bin/vacuumdb -d mapdb -z -v

Craig James wrote:
>
> On 2007-06-11 Christo Du Preez wrote:
>> I really hope someone can shed some light on my problem. I'm not sure
>> if this is a posgres or potgis issue.
>>
>> Anyway, we have 2 development laptops and one live server, somehow I
>> managed to get the same query to perform very well om my laptop, but
>> on both the server and the other laptop it's really performing bad.
>
> One simple possibility that bit me in the past: If you do
> pg_dump/pg_restore to create a copy of the database, you have to
> ANALYZE the newly-restored database. I mistakenly assumed that
> pg_restore would do this, but you have to run ANALYZE explicitely
> after a restore.
>
> Craig
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

--
Christo Du Preez

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


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Where do I set the planner settings or are you reffering to settings in
postgres.conf that may affect the planner?

The one badly performing laptop is the same as mine (the fast one) and
the server is much more powerful.

Laptops: Intel Centrino Duo T2600 @ 2.16GHz, 1.98 GB RAM

Server: 2 xIntel Pentium D CPU 3.00GHz, 4 GB RAM

All three systems are running Suse 10.2, with the same PosgreSQL, same
configs, same databases. As far as I know, same everything.

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

Thanx for all the advice

Dave Dutcher wrote:
>> -----Original Message-----
>> From: Christo Du Preez
>> Sent: Monday, June 11, 2007 10:10 AM
>>
>> I have narrowed down the problem (I think) and it's the query
>> planner using different plans and I haven't got a clue why.
>> Can anyone please shed some light on this?
>>
>
> Different plans can be caused by several different things like different
> server versions, different planner settings in the config file, different
> schemas, or different statistics. You say the server versions are the same,
> so that's not it. Is the schema the same? One isn't missing indexes that
> the other has? Do they both have the same data, or at least very close to
> the same data? Have you run analyze on both of them to update their
> statistics? Do they have the same planner settings in the config file? I
> would check that stuff out and see if it helps.
>
> Dave
>
>
>
>

--
Christo Du Preez

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> From: Christo Du Preez
> Sent: Tuesday, June 12, 2007 2:38 AM
>
> Where do I set the planner settings or are you reffering to
> settings in postgres.conf that may affect the planner?
>

Yes I'm reffering to settings in postgres.conf. I'm wondering if
enable_indexscan or something got turned off on the server for some reason.
Here is a description of those settings:

http://www.postgresql.org/docs/8.2/i...fig-query.html

So when you move data from the laptop to the server, I see that your script
correctly runs an analyze after the load, so have you run analyze on the
fast laptop lately? Hopefully running analyze wouldn't make the planner
choose a worse plan on the laptop, but if we are trying to get things
consistant between the laptop and server, that is something I would try.

If the consistancy problem really is a problem of the planner not using
index scans on the server, then if you can, please post the table definition
for the table with a million rows and an EXPLAIN ANALYZE of a query which
selects a few rows from the table.

Dave


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings