I am trying to make some calculations using PostGIS and PostgreSQL. There are 3 important tables : wbn, wgo and wvb. These tables have about 500.000 objects in them (geometries). The problem is that my calculations are taking way to much time. I think I found the query's responsible for this;
INSERT INTO bio_schema.wgotemp1(wbn_uidn,wgo_uidn) ( SELECT gdi_agiv_schema.wbn.uidn, gdi_agiv_schema.wgo.uidn FROM gdi_agiv_schema.wbn, gdi_agiv_schema.wgo WHERE ST_Intersects(gdi_agiv_schema.wgo.the_geom, gdi_agiv_schema.wbn.the_geom)='t' AND ST_Length(ST_Intersection(gdi_agiv_schema.wgo.the_geom, gdi_agiv_schema.wbn.the_geom)) > 2 AND gdi_agiv_schema.wbn.uidn = f_uidn AND gdi_agiv_schema.wgo.type = f_type );
INSERT INTO bio_schema.tempwvb(wbn_uidn,wvb_uidn,sum) ( SELECT gdi_agiv_schema.wbn.uidn, gdi_agiv_schema.wvb.uidn, ST_Length(ST_Intersection(gdi_agiv_schema.wbn.the_geom, gdi_agiv_schema.wvb.the_geom)) FROM gdi_agiv_schema.wbn, gdi_agiv_schema.wvb WHERE ST_Intersects(gdi_agiv_schema.wbn.the_geom, gdi_agiv_schema.wvb.the_geom)='t' AND gdi_agiv_schema.wbn.uidn = f_uidn );
What they are doing is looking for geometries (from the tables wgo and wvb) located in a certain polygon (from table wgo). This takes a lot of time, but for my calculations it is absolutely necessary to know which objects from the tables wgo and wvb are located inside this polygon and what their length is.
What can I do to speed up my queries?