[postgis-users] basic question about indexing

[Markus Schaber]

Hi, Sandeep,

Sandeep Kumar Jakkaraju wrote:

> say i have a Spatial Table with geometry column "location" say of
> POINT() type ... which represents the current location of each user !!
> they are changing with time ...
>  
> My Question is *Do I have to index the table every time the location
> column is updated* ???

No, the created index will be maintained when you update the table.

There are some small caveats:

- All PostgreSQL versions will need regular VACUUM runs. Newer versions
bring the Autovacuum daemon, newest versions have it included in the
backend. Do care to configure that appropriately. Also make sure that
the free_space_map setting is large enough. (See the standard PostgreSQL
tuning / maintainance documentation for more tips.)

- Older PostgreSQL versions might suffer from index bload for some
specific usage patterns - you can see that when the disk usage for
indices is constantly growing, and speed is getting slower and slower.
For those cases, a regular REINDEX command is helpful. As far as I can
tell, 8.1 has fixed those issues.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org