Re: Win32 PostgreSQL 7.5/PostGIS snapshot

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Win32 PostgreSQL 7.5/PostGIS snapshot

Mark Cave-Ayland-2
Hi Emil,

Thanks for the feedback regarding the performance of the Win32
PGSQL/PostGIS snapshot - it's good to hear that someone else has managed
to get it working successfully. It will be interesting to see if other
people experience the same level of performance that you have, and how
different it will be to tune PostgreSQL for Win32. I'll keep an eye on
the list for other reports of BSOD when restarting while the server is
still active, and if it is a problem then I shall mention it to the
PostgreSQL-Win32 people.


Many thanks,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On
> Behalf Of Emil Zegers
> Sent: 28 May 2004 11:49
> To: [hidden email]
> Subject: [postgis-users] Re: Win32 PostgreSQL 7.5/PostGIS snapshot
>
>
> Hello Mark,
>
> Your Win32 PostgreSQL 7.5/PostGIS snapshot is working great!
>
> This morning I downloaded and installed the newest version
> with GiST index patch.
>
> Imported some shapefile data (point file with >88000 points)
> and made indexes on it.
>
> I'm viewing the data through a MapServer client.
>
> Performance is great and the database is stable too on my
> Windows 2000 system.
>
> About the batch files running order: I run env.bat before
> initdb.bat otherwise the path is not correct.
>
> Thanx for the 'Ctrl-C' tip to shutdown the server.
>
> If I restart the system without shutting down I get a BSOD
> before rebooting.
>
> Again, thanks for sharing this software.
>
> Best regards,
>
>
>
> Groeten,
>
> Emil Zegers
>
> De Straat Milieu-adviseurs
> Divisie Informatisering
> 026 7513902
> 06 22965585
> http://www.destraat.nl
>
>
>
> ______________________________________________________
>
>  This message has been checked for all known viruses
> ______________________________________________________
> _______________________________________________
> postgis-users mailing list [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



Reply | Threaded
Open this post in threaded view
|

Re: Win32 PostgreSQL 7.5/PostGIS snapshot

Romi Hardiyanto

Well, this is a very great work. I didn't notice the index throw errors
when uploading my road data.

Many thanks.

Romi H

Mark Cave-Ayland wrote:

> Hi Emil,
>
> Thanks for the feedback regarding the performance of the Win32
> PGSQL/PostGIS snapshot - it's good to hear that someone else has managed
> to get it working successfully. It will be interesting to see if other
> people experience the same level of performance that you have, and how
> different it will be to tune PostgreSQL for Win32. I'll keep an eye on
> the list for other reports of BSOD when restarting while the server is
> still active, and if it is a problem then I shall mention it to the
> PostgreSQL-Win32 people.
>
>
> Many thanks,
>
> Mark.
>
> ---
>
> Mark Cave-Ayland
> Webbased Ltd.
> Tamar Science Park
> Derriford
> Plymouth
> PL6 8BX
> England
>
> Tel: +44 (0)1752 764445
> Fax: +44 (0)1752 764446
>
>
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender. You
> should not copy it or use it for any purpose nor disclose or distribute
> its contents to any other person.
>
>
>>-----Original Message-----
>>From: [hidden email]
>>[mailto:[hidden email]] On
>>Behalf Of Emil Zegers
>>Sent: 28 May 2004 11:49
>>To: [hidden email]
>>Subject: [postgis-users] Re: Win32 PostgreSQL 7.5/PostGIS snapshot
>>
>>
>>Hello Mark,
>>
>>Your Win32 PostgreSQL 7.5/PostGIS snapshot is working great!
>>
>>This morning I downloaded and installed the newest version
>>with GiST index patch.
>>
>>Imported some shapefile data (point file with >88000 points)
>>and made indexes on it.
>>
>>I'm viewing the data through a MapServer client.
>>
>>Performance is great and the database is stable too on my
>>Windows 2000 system.
>>
>>About the batch files running order: I run env.bat before
>>initdb.bat otherwise the path is not correct.
>>
>>Thanx for the 'Ctrl-C' tip to shutdown the server.
>>
>>If I restart the system without shutting down I get a BSOD
>>before rebooting.
>>
>>Again, thanks for sharing this software.
>>
>>Best regards,
>>
>>
>>
>>Groeten,
>>
>>Emil Zegers
>>
>>De Straat Milieu-adviseurs
>>Divisie Informatisering
>>026 7513902
>>06 22965585
>>http://www.destraat.nl
>>
>>
>>
>>______________________________________________________
>>
>> This message has been checked for all known viruses
>>______________________________________________________
>>_______________________________________________
>>postgis-users mailing list [hidden email]
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>


Reply | Threaded
Open this post in threaded view
|

more query optimization

Robert W. Burgholzer-2
I am trying to do a join using views, and am having some serious
performance issues as a result.

I have a postgis table, with soil map shapes and ID's. I have three other
tables that when linked together in a view give the names and
characteristics of the soils in the soil map, and the variation of the
characteristics by soil layer, indexed by a key they share with the soil
shape table.

All of these layers can be queried fairly quickly, although my soil map
table has around a half million entries in it, with about 4,000 unique ID's
that map to the other two data tables. When I just pull the soil layer
shapes into my arcview view (using Armin Burger's avpgconn) without joining
on the property data, the process takes about 10-20 seconds depending on
the width of the view. When I pull it with one soil layer (the top layer
only) properties view joined, I get about 10 minutes as the result, and
when I join both the top soils layer view and the second soil layer view my
query time is about 15-20 minutes.

I surmise from the query explain output that the join on the key properties
between these layers is occurring before the soil shape records are weeded
out to the spatial extent (which is a very low cost selection since they
are indexed), thus, all half million records are being joined first, then
the ones in the spatial extent are being grabbed. What I would like to do
is reverse this order. Does anyone have any idea how to do this?

Thanks in advance.

The results of EXPLAIN commands on the two views, and on the query that
joins them are as follows:

*************************************
***** the soil shape query *******
Data_Soils=# explain select * from ssurgo_shp where "MUKEY" = '118512';
                                      QUERY
PLAN
------------------------------------------------------------------------------------
  Index Scan using ssmukey_ix on ssurgo_shp  (cost=0.00..1074.82 rows=280
width=227)
    Index Cond: ("MUKEY" = '118512'::bpchar)
(2 rows)


*************************************
**** the soil property query *****
Data_Soils=# explain select * from ssurgoprops_l1 where mukey = '118512';
                                                   QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
  Subquery Scan ssurgoprops_l1  (cost=0.00..172.51 rows=1 width=87)
    ->  Aggregate  (cost=0.00..172.51 rows=1 width=87)
          ->  Group  (cost=0.00..172.39 rows=3 width=87)
                ->  Nested Loop  (cost=0.00..172.38 rows=3 width=87)
                      Join Filter: ("outer".hydgrp = "inner".hydgrp)
                      ->  Nested Loop  (cost=0.00..109.91 rows=2 width=63)
                            ->  Index Scan using compmukey_ix on
component  (cost=0.00..20.79 rows=5 width=34)
                                  Index Cond: (mukey = '118512'::character
varying)
                            ->  Index Scan using ch_cokey_ix on
chorizon  (cost=0.00..18.18 rows=1 width=29)
                                  Index Cond: (chorizon.cokey = "outer".cokey)
                                  Filter: (desgnvert = 1)
                      ->  Seq Scan on hspfinfilt  (cost=0.00..20.00
rows=1000 width=24)


*************************************
**** the combination query ******
Data_Soils=# explain SELECT
musym,mukey,infilt,depth,clay_pct,sand_pct,ksat,thetasat,thetafc,hydgrp,uzsn,lzsn,the_geom
FROM soilpropsdd_l1 WHERE the_geom && GeometryFromText('BOX3D(-77.765306
36.944388,-77.408085 37.306579)'::box3d,-1);
                                                            QUERY PLAN

--------------------------------------------------------------------------------
-------------------------------------------------
  Nested Loop  (cost=4843.38..5106.68 rows=2 width=303)
    Join Filter: ("outer".mukey = "inner".mukey)
    ->  Merge Join  (cost=2544.81..2547.60 rows=1 width=158)
          Merge Cond: ("outer".mukey = "inner".mukey)
          ->  Sort  (cost=11.78..11.79 rows=2 width=77)
                Sort Key: ssurgo_shp.mukey
                ->  Index Scan using soildd_ix on
ssurgo_shp  (cost=0.00..11.77 r
ows=2 width=77)
                      Index Cond: (wkb_geometry &&
'SRID=-1;BOX3D(-77.765306 36.9
44388 0,-77.408085 37.306579 0)'::geometry)
          ->  Sort  (cost=2533.03..2534.41 rows=553 width=87)
                Sort Key: ssurgoprops_l2.mukey
                ->  Subquery Scan ssurgoprops_l2  (cost=2273.01..2507.86
rows=553
  width=87)
                      ->  Aggregate  (cost=2273.01..2507.86 rows=553 width=87)
                            ->  Group  (cost=2273.01..2300.64 rows=5526
width=87)
                                  ->  Sort  (cost=2273.01..2286.82
rows=5526 widt
h=87)
                                        Sort Key: component.mukey
                                        ->  Merge
Join  (cost=1840.18..1929.52 ro
ws=5526 width=87)
                                              Merge Cond: ("outer".hydgrp =
"inne
r".hydgrp)
                                              ->  Sort
(cost=1770.35..1777.99 ro
ws=3053 width=63)
                                                    Sort Key: component.hydgrp
                                                    ->  Hash
Join  (cost=847.30..
1593.65 rows=3053 width=63)
                                                          Hash Cond:
("outer".cok
ey = "inner".cokey)
                                                          ->  Seq Scan on
chorizo
n  (cost=0.00..685.29 rows=3053 width=29)
                                                                Filter:
(desgnver
t = 2)
                                                          ->  Hash
(cost=473.64.
.473.64 rows=8364 width=34)
                                                                ->  Seq
Scan on c
omponent  (cost=0.00..473.64 rows=8364 width=34)
                                              ->  Sort  (cost=69.83..72.33
rows=1
000 width=24)
                                                    Sort Key: hspfinfilt.hydgrp
                                                    ->  Seq Scan on
hspfinfilt  (
cost=0.00..20.00 rows=1000 width=24)
    ->  Subquery Scan ssurgoprops_l1  (cost=2298.57..2551.64 rows=595 width=87)
          ->  Aggregate  (cost=2298.57..2551.64 rows=595 width=87)
                ->  Group  (cost=2298.57..2328.34 rows=5955 width=87)
                      ->  Sort  (cost=2298.57..2313.45 rows=5955 width=87)
                            Sort Key: component.mukey
                            ->  Merge Join  (cost=1829.33..1925.21
rows=5955 widt
h=87)
                                  Merge Cond: ("outer".hydgrp = "inner".hydgrp)
                                  ->  Sort  (cost=1759.50..1767.72
rows=3290 widt
h=63)
                                        Sort Key: component.hydgrp
                                        ->  Hash
Join  (cost=816.22..1567.31 rows
=3290 width=63)
                                              Hash Cond: ("outer".cokey =
"inner"
.cokey)
                                              ->  Seq Scan on
chorizon  (cost=0.0
0..685.29 rows=3290 width=29)
                                                    Filter: (desgnvert = 1)
                                              ->  Hash
(cost=473.64..473.64 rows
=8364 width=34)
                                                    ->  Seq Scan on
component  (c
ost=0.00..473.64 rows=8364 width=34)
                                  ->  Sort  (cost=69.83..72.33 rows=1000
width=24
)
                                        Sort Key: hspfinfilt.hydgrp
                                        ->  Seq Scan on
hspfinfilt  (cost=0.00..2
0.00 rows=1000 width=24)
(46 rows)




Robert Burgholzer
Environmental Engineer
MapTech Inc.
http://www.maptech-inc.com/ 


Reply | Threaded
Open this post in threaded view
|

Re: more query optimization

Paul Ramsey-2
Do you have USE_STATS on, and have you run the update statistics
function? That is the magic incantation, generally...

On Tuesday, June 1, 2004, at 06:41 AM, Robert W. Burgholzer wrote:

> I am trying to do a join using views, and am having some serious
> performance issues as a result.
>
> I have a postgis table, with soil map shapes and ID's. I have three
> other tables that when linked together in a view give the names and
> characteristics of the soils in the soil map, and the variation of the
> characteristics by soil layer, indexed by a key they share with the
> soil shape table.
>
> All of these layers can be queried fairly quickly, although my soil
> map table has around a half million entries in it, with about 4,000
> unique ID's that map to the other two data tables. When I just pull
> the soil layer shapes into my arcview view (using Armin Burger's
> avpgconn) without joining on the property data, the process takes
> about 10-20 seconds depending on the width of the view. When I pull it
> with one soil layer (the top layer only) properties view joined, I get
> about 10 minutes as the result, and when I join both the top soils
> layer view and the second soil layer view my query time is about 15-20
> minutes.
>
> I surmise from the query explain output that the join on the key
> properties between these layers is occurring before the soil shape
> records are weeded out to the spatial extent (which is a very low cost
> selection since they are indexed), thus, all half million records are
> being joined first, then the ones in the spatial extent are being
> grabbed. What I would like to do is reverse this order. Does anyone
> have any idea how to do this?
>
> Thanks in advance.
>

      Paul Ramsey
      Refractions Research
      Email: [hidden email]
      Phone: (250) 885-0632


Reply | Threaded
Open this post in threaded view
|

Re: more query optimization

Robert W. Burgholzer-2
OK,
the USE_STATS option was indeed set to 1 in my postgis makefile, I have run
the following query:

select update_geometry_stats();

And voila! the query comes back in under a minute,  perfectly acceptable.
Now, do you suggest I include this update_geometry_stats() call in my daily
vacuum job?

Thanks a bunch,
r.b.

At 07:33 AM 6/1/2004 -0700, you wrote:

>Do you have USE_STATS on, and have you run the update statistics function?
>That is the magic incantation, generally...
>
>On Tuesday, June 1, 2004, at 06:41 AM, Robert W. Burgholzer wrote:
>
>>I am trying to do a join using views, and am having some serious
>>performance issues as a result.
>>
>>I have a postgis table, with soil map shapes and ID's. I have three other
>>tables that when linked together in a view give the names and
>>characteristics of the soils in the soil map, and the variation of the
>>characteristics by soil layer, indexed by a key they share with the soil
>>shape table.
>>
>>All of these layers can be queried fairly quickly, although my soil map
>>table has around a half million entries in it, with about 4,000 unique
>>ID's that map to the other two data tables. When I just pull the soil
>>layer shapes into my arcview view (using Armin Burger's avpgconn) without
>>joining on the property data, the process takes about 10-20 seconds
>>depending on the width of the view. When I pull it with one soil layer
>>(the top layer only) properties view joined, I get about 10 minutes as
>>the result, and when I join both the top soils layer view and the second
>>soil layer view my query time is about 15-20 minutes.
>>
>>I surmise from the query explain output that the join on the key
>>properties between these layers is occurring before the soil shape
>>records are weeded out to the spatial extent (which is a very low cost
>>selection since they are indexed), thus, all half million records are
>>being joined first, then the ones in the spatial extent are being
>>grabbed. What I would like to do is reverse this order. Does anyone have
>>any idea how to do this?
>>
>>Thanks in advance.
>
>      Paul Ramsey
>      Refractions Research
>      Email: [hidden email]
>      Phone: (250) 885-0632
>
>_______________________________________________
>postgis-users mailing list
>[hidden email]
>http://postgis.refractions.net/mailman/listinfo/postgis-users

Robert Burgholzer
Environmental Engineer
MapTech Inc.
http://www.maptech-inc.com/ 


Reply | Threaded
Open this post in threaded view
|

Re: more query optimization

David Blasby-3
Robert W. Burgholzer wrote:
> OK,
> the USE_STATS option was indeed set to 1 in my postgis makefile, I have
> run the following query:
>
> select update_geometry_stats();
>
> And voila! the query comes back in under a minute,  perfectly
> acceptable. Now, do you suggest I include this update_geometry_stats()
> call in my daily vacuum job?


You only need to call update_geometry_stats() if your data significantly
changes.  Basically, it build up a 2d histogram of your data, and uses
this to estimate the number of results in a given query.  You only have
to update it if the histogram is no longer an accurate reflection of
your data.


dave