May it is best to try to understand what this command does and that will
help you understand how to use it. First, I'm not an expert on this, so
I fear the my description may have some of my own misconceptions in it,
but here goes:
vacuum and analyze are actually two separate operations but they can be
run at the same time.
Vacuum is the operations of recovering free space and compacting data
into pages. When you do operations on a table like update and delete,
records get rewritten and the old row version is marked as dead. If you
want to retrieve 5 rows and they all happen to be on the same page ,
then you only need to read one page of data, but if updates and
deletions have fragmented you data across multiple pages, then to fetch
all 5 records you need to load multiple pages which is more costly. The
same thing happens to indexes as you update, delete and add records to a
table.
I have a lot of databases where a prepare data initially and then the
data is only queried repeatedly. So after I'm done preparing it I vacuum
analyze it once and I'm done.
Analyze typically scans some percentage of the rows of a table and
updates the statistics about the table. This helps the query planner
make the correct assumptions about how to plan a query's execution in
the most efficient manner.
You can ANALYZE table without running VACUUM ANALYZE table and this
is fast.
Hope this helps.
-Steve
On 2/14/2012 7:38 PM, Sheara Cohen wrote:
Hi –
I’m a relatively new user to PostGIS/Postgres and was told that I should
use Vacuum Analyze whenever a I create a new table. I have been
discovering, however, that the time it takes to run vacuum analyze on a
table is often **MUCH** greater than the time saved during dependent
operations.
So, this makes me wonder when it is really critical to run Vacuum
Analyze vs. when it is not worth it. (I’m leaning towards not worth it
the vast majority of the time.)
For instance, is it important to run AFTER…
·deleting rows?
·inserting rows?
·running st_union?
·intersecting two geometries?
Or perhaps BEFORE…
·intersecting two geometries
·joining two or more tables
·making row selections and column calculations
Etc…
Thank you!
Sheara
*Sheara Cohen*
Planner
*C A L T H O R P E**A S S O C I A T E S*
2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA
510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax)
she...@calthorpe.com mailto:she...@calthorpe.com | www.calthorpe.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
