speed up st_within query in postgresql

1. Create index
CREATE INDEX idx_tablename_columnname ON tablename USING GIST(columnname);

This will create a spatial index for the geometry column [columnname] in the [tablename].  According to postgis manual, it creates the bbox for each geometry in the table to speed up the query.

2. Cluster
CLUSTER

After index created, cluster the table to arrange the similar data in to the same disk space.

3. Optional: simplify the geometries.
SELECT ST_NPOINTS(geom_column) AS npoints FROM tablename ORDER BY npoints DESC LIMIT 25;

SELECT ST_SIMPLIFY(geom_column, number_scale) AS simpgeom FROM tablename;

Check the number of points in the 25 biggest geometries.  If they are too big, the speed of st_within query will be slow. You can try simplify the geometries in the table if possible.  Simplifying means reduce the points in geometries.

4. Test
EXPLAIN UPDATE locationtable SET columnname = (
SELECT columnname FROM regiontable
WHERE ST_WITHIN(geom.locationtable , geom.regiontable)
)

Explain breaks down the query into plan and evaluate if an index is needed or not.  Through the EXPLAIN, you can find out if the query is efficient enough.

5. Dissolve multi-polygon to polygon