I am working with 3 tables with 3 x 500 000 rows. There is one column that gives each object a unique ID (UIDN), there also is a column with the geom data (the_geom). There also are some other columns but the 2 above are the most important.
When I run my code, it takes a very long time, beceause these 3 tables are so big (I tested the same code with smaller columns and there it runs much faster, so I know for sure that the problem is the size of these columns).
PostGIS automatically made a GIST index for the 'the_geom' columns. Beceause it was real slow, I added a btree index for the 'uidn' column. I hoped this would speed things up, but it doesn't.
What do I need to do? Do I need another type of index?
Here is an example of a real query found in my function
SELECT wbn.uidn, wgo.uidn FROM wbn, wgo WHERE ST_Intersects (wgo.the_geom, wbn.the_geom) = 't' AND ST_Length (ST_Intersection (wgo.the_geom, wbn.the_geom)) > 2 AND wbn.uidn = f_uidn AND wgo.type = f_type
wbn and wgo are two of the big tables I was talking about