[postgis-users] basic question about indexing

[Gregory S. Williamson]

Sandeep --

With recent versions of postGIS (1.x) you don't have to reindex for each update ... there were fixes to the GIST indexing that happened last spring, so if you have an up-to-date version you should be ok.

But !!

An update of a row in postgres creates a new row and a the old one; during the update other processes will see the old row; once the update is finished the old row is marked as dead and everybody sees the new row.

The indexes, meantime, have to get shuffled some to reflect the new data, and this also results in dead index rows. 

More dead rows decreases performance because any operation needs to consider whther a row is dead or alive, and it also bloats memory use.

Running frequent VACUUM ANALYZE commands (in 8.1 -- not sure about 8.0 -- and beyond there is an autovac feature that can automate a lot of this) helps minimize the bad effects of the dead rows for data.

Lots of frequent updates means you need to vacuum frequently.

Periodically I would recommend a REINDEX of the afflicted tables to lose the dead index rows ... this command, alas, locks the table even for read-only transactions, I think, so it needs to be scheduled for down-time [someone correct me if I am wrong on this -- I'd love to be corrected!]. For smallish tables with few indexes this is fast, more indexes and more tables it is slower, but still a good thing because it helps runtime performance.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Sandeep Kumar Jakkaraju
Sent:	Wed 12/6/2006 1:54 AM
To:	PostGIS Users Discussion
Cc:	
Subject:	[postgis-users] basic question about indexing

Hi All

I have a basic question about indexing ....


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* ???


Thanx in advance.

Sandeep



-------------------------------------------------------
Click link below if it is SPAM gsw at globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4576931b117806672479766&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:4576931b117806672479766!
-------------------------------------------------------