[postgis-users] optimizing intersection of two large feature classes

andreas_bfw Andreas.Schild at bfw.gv.at
Mon Aug 25 00:43:05 PDT 2008


Dear List,

I'm a newby to Postgres/Postgis.
I have 2 feature classes, #)soilunits of austria 500000+ polygons which I
reduced to about 13000 multipolygons,
#)statistic units of austria 1300000 polygons (regular grid )
I have to do an intersection on those feature classes.

Therefor I did some tuning: Postgres Parameters such as shared_buffers, also
increased shmmax of the linux kernel
since EXPLAIN ANALYZE on a subset of the feature classes I used ST_ADDBBox
on both feature classes.

However, since the query is running now for 2 days, i am not sure if I have
done the query well.
Below is my query and the explain verbose output

I would be very happy about any help or tip

Thanks a lot in advance,

Andi


***************
CREATE TABLE lwbk.bodenform_r250_poly2 AS SELECT
ST_Intersection(r.wkb_geometry, b.wkb_geometry) AS wbk_geometry,
r.r_250m, b.bofo
FROM
r250_bbox_test AS  r, lwbk.bofo_u_b_bbox_test AS b
WHERE  ST_Intersects(r.wkb_geometry, b.wkb_geometry) AND b.bofo NOT IN
(73900, 145900);
***************
"   {NESTLOOP "
"   :startup_cost 0.00 "
"   :total_cost 600611.05 "
"   :plan_rows 1847088 "
"   :plan_width 26874 "
"   :targetlist ("
"      {TARGETENTRY "
"      :expr "
"         {FUNCEXPR "
"         :funcid 17620 "
"         :funcresulttype 17160 "
"         :funcretset false "
"         :funcformat 0 "
"         :args ("
"            {VAR "
"            :varno 65000 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 2"
"            }"
"            {VAR "
"            :varno 65001 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 2"
"            }"
"         )"
"         }"
"      :resno 1 "
"      :resname wbk_geometry "
"      :ressortgroupref 0 "
"      :resorigtbl 0 "
"      :resorigcol 0 "
"      :resjunk false"
"      }"
"      {TARGETENTRY "
"      :expr "
"         {VAR "
"         :varno 65000 "
"         :varattno 3 "
"         :vartype 1042 "
"         :vartypmod 14 "
"         :varlevelsup 0 "
"         :varnoold 1 "
"         :varoattno 3"
"         }"
"      :resno 2 "
"      :resname r_250m "
"      :ressortgroupref 0 "
"      :resorigtbl 275458 "
"      :resorigcol 3 "
"      :resjunk false"
"      }"
"      {TARGETENTRY "
"      :expr "
"         {VAR "
"         :varno 65001 "
"         :varattno 1 "
"         :vartype 23 "
"         :vartypmod -1 "
"         :varlevelsup 0 "
"         :varnoold 2 "
"         :varoattno 1"
"         }"
"      :resno 3 "
"      :resname bofo "
"      :ressortgroupref 0 "
"      :resorigtbl 253892 "
"      :resorigcol 1 "
"      :resjunk false"
"      }"
"   )"
"   :qual <> "
"   :lefttree "
"      {SEQSCAN "
"      :startup_cost 0.00 "
"      :total_cost 933.83 "
"      :plan_rows 24544 "
"      :plan_width 26747 "
"      :targetlist ("
"         {TARGETENTRY "
"         :expr "
"            {VAR "
"            :varno 2 "
"            :varattno 1 "
"            :vartype 23 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 1"
"            }"
"         :resno 1 "
"         :resname <> "
"         :ressortgroupref 0 "
"         :resorigtbl 0 "
"         :resorigcol 0 "
"         :resjunk false"
"         }"
"         {TARGETENTRY "
"         :expr "
"            {VAR "
"            :varno 2 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 2"
"            }"
"         :resno 2 "
"         :resname <> "
"         :ressortgroupref 0 "
"         :resorigtbl 0 "
"         :resorigcol 0 "
"         :resjunk false"
"         }"
"      )"
"      :qual ("
"         {SCALARARRAYOPEXPR "
"         :opno 518 "
"         :opfuncid 144 "
"         :useOr false "
"         :args ("
"            {VAR "
"            :varno 2 "
"            :varattno 1 "
"            :vartype 23 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 1"
"            }"
"            {CONST "
"            :consttype 1007 "
"            :constlen -1 "
"            :constbyval false "
"            :constisnull false "
"            :constvalue 32 [ 32 0 0 0 1 0 0 0 0 0 0 0 23 0 0 0 2 0 0 0 1 0
0 0"
"             -84 32 1 0 -20 57 2 0 ]"
"            }"
"         )"
"         }"
"      )"
"      :lefttree <> "
"      :righttree <> "
"      :initPlan <> "
"      :extParam (b)"
"      :allParam (b)"
"      :nParamExec 0 "
"      :scanrelid 2"
"      }"
"   :righttree "
"      {INDEXSCAN "
"      :startup_cost 0.00 "
"      :total_cost 24.03 "
"      :plan_rows 14 "
"      :plan_width 127 "
"      :targetlist ("
"         {TARGETENTRY "
"         :expr "
"            {VAR "
"            :varno 1 "
"            :varattno 1 "
"            :vartype 23 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 1"
"            }"
"         :resno 1 "
"         :resname <> "
"         :ressortgroupref 0 "
"         :resorigtbl 0 "
"         :resorigcol 0 "
"         :resjunk false"
"         }"
"         {TARGETENTRY "
"         :expr "
"            {VAR "
"            :varno 1 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 2"
"            }"
"         :resno 2 "
"         :resname <> "
"         :ressortgroupref 0 "
"         :resorigtbl 0 "
"         :resorigcol 0 "
"         :resjunk false"
"         }"
"         {TARGETENTRY "
"         :expr "
"            {VAR "
"            :varno 1 "
"            :varattno 3 "
"            :vartype 1042 "
"            :vartypmod 14 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 3"
"            }"
"         :resno 3 "
"         :resname <> "
"         :ressortgroupref 0 "
"         :resorigtbl 0 "
"         :resorigcol 0 "
"         :resjunk false"
"         }"
"      )"
"      :qual ("
"         {OPEXPR "
"         :opno 17298 "
"         :opfuncid 17287 "
"         :opresulttype 16 "
"         :opretset false "
"         :args ("
"            {VAR "
"            :varno 1 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 2"
"            }"
"            {VAR "
"            :varno 65001 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 2"
"            }"
"         )"
"         }"
"      )"
"      :lefttree <> "
"      :righttree <> "
"      :initPlan <> "
"      :extParam (b)"
"      :allParam (b)"
"      :nParamExec 0 "
"      :scanrelid 1 "
"      :indexid 275465 "
"      :indexqual ("
"         {OPEXPR "
"         :opno 17298 "
"         :opfuncid 17287 "
"         :opresulttype 16 "
"         :opretset false "
"         :args ("
"            {VAR "
"            :varno 1 "
"            :varattno 1 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 2"
"            }"
"            {VAR "
"            :varno 65001 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 2"
"            }"
"         )"
"         }"
"      )"
"      :indexqualorig ("
"         {OPEXPR "
"         :opno 17298 "
"         :opfuncid 17287 "
"         :opresulttype 16 "
"         :opretset false "
"         :args ("
"            {VAR "
"            :varno 1 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 1 "
"            :varoattno 2"
"            }"
"            {VAR "
"            :varno 65001 "
"            :varattno 2 "
"            :vartype 17160 "
"            :vartypmod -1 "
"            :varlevelsup 0 "
"            :varnoold 2 "
"            :varoattno 2"
"            }"
"         )"
"         }"
"      )"
"      :indexstrategy (i 3)"
"      :indexsubtype (o 0)"
"      :indexorderdir 0"
"      }"
"   :initPlan <> "
"   :extParam (b)"
"   :allParam (b)"
"   :nParamExec 0 "
"   :jointype 0 "
"   :joinqual ("
"      {FUNCEXPR "
"      :funcid 17654 "
"      :funcresulttype 16 "
"      :funcretset false "
"      :funcformat 0 "
"      :args ("
"         {VAR "
"         :varno 65000 "
"         :varattno 2 "
"         :vartype 17160 "
"         :vartypmod -1 "
"         :varlevelsup 0 "
"         :varnoold 1 "
"         :varoattno 2"
"         }"
"         {VAR "
"         :varno 65001 "
"         :varattno 2 "
"         :vartype 17160 "
"         :vartypmod -1 "
"         :varlevelsup 0 "
"         :varnoold 2 "
"         :varoattno 2"
"         }"
"      )"
"      }"
"   )"
"   }"
""
"Nested Loop  (cost=0.00..600611.05 rows=1847088 width=26874)"
"  Join Filter: _st_intersects(r.wkb_geometry, b.wkb_geometry)"
"  ->  Seq Scan on bofo_u_b_bbox_test b  (cost=0.00..933.83 rows=24544
width=26747)"
"        Filter: (bofo <> ALL ('{73900,145900}'::integer[]))"
"  ->  Index Scan using r250_bbox_test_gist1 on r250_bbox_test r 
(cost=0.00..24.03 rows=14 width=127)"
"        Index Cond: (r.wkb_geometry && b.wkb_geometry)"
"        Filter: (r.wkb_geometry && b.wkb_geometry)"

-- 
View this message in context: http://www.nabble.com/optimizing-intersection-of-two-large-feature-classes-tp19107439p19107439.html
Sent from the PostGIS - User mailing list archive at Nabble.com.




More information about the postgis-users mailing list