How can I speed up these queries

Question

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

            );

AND

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?

Thanks

Answer 1

Create GiST index on tables

CREATE INDEX name ON table USING gist(column);

Answer 2

This could speeding up your query.

INSERT INTO bio_schema.tempwvb(wbn_uidn,wvb_uidn,sum) 
(
SELECT
    gdi_agiv_schema.wbn.uidn,
    gdi_agiv_schema.wvb.uidn,
    ST_Length(gdi_agiv_schema.wbn.the_geom)
FROM gdi_agiv_schema.wbn INNER JOIN gdi_agiv_schema.wvb
    ON ST_Intersects(gdi_agiv_schema.wbn.the_geom, gdi_agiv_schema.wvb.the_geom)
        AND gdi_agiv_schema.wbn.uidn = f_uidn);

When you want to know the length of an object that lays fully within a polygon you ask if there a intersecting and query the length of this object.

When you want to know the intersection geometry of an object that intersects the boundaries (the hull) of the polygon you have to use ST_Intersection. I can see, at the moment, no enhancement regarding to the query speed.