[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