1

I was taught that you need to 'Analyze' each dataset in enterprise databases as much as you can. It was one of those things that I just did, and which I never thought much about. However, I recently looked up the tool I always use, to see what it actually does: ArcGIS Pro Analyze.

It states:

Updates database statistics of business tables, feature tables, and delta tables, along with the statistics of those tables' indexes.

Does this really impact any performance in a measurable way?

Is this something that really is needed?

It seems to me that it is something that could be done (and slightly improve database performance), but really does not do a lot.

3
  • 1
    usually done as the last step in a workflow (where data changes) gis.stackexchange.com/questions/88609/… (it helps the database order).
    – Mapperz
    Feb 9 at 3:10
  • 2
    If you only do full-table scans, it's rare to see benefits of an ANALYZE. Where it really makes a difference is when you have a few million rows of data and multiple singleton indexes, at which point the query optimizer (within the RDBMS) will be able to choose the best index to follow.
    – Vince
    Feb 9 at 3:22
  • 2
    This asks multiple questions and any answer would be an opinion Feb 9 at 4:00
1

@Mapperz answered this. Its usually done as the last step in a workflow when data changes. See here for more: What is the correct order for compressing, rebuilding indexes and updating statistics in ArcGIS?

1

An anecdotal response here: My first foray into using Open Source GIS tools and PostGRES/PostGIS was around PostGIS 7.x on a very small instance ..I was trying to make a little web mapping app that used PHP to spatially query features from PostGIS, then render the feature vectors in the map so that they supported user interactions. When I first got it working, it was ultra slow because the query was slow ..even though I had a spatial index on the table. This was perplexing. I got to Googling and ran into a recommendation to run VACUUM ANALYZE %TABLE_NAME%; ..so I tried it. And WHOA! It's difficult to exaggerate the performance improvement it effected. It went from borderline unusable to look-at-it-go!

These days the software is about a decade more evolved, and I'm using considerably more robust systems, and the whole architecture is probably more tolerant of loosely admin'd databases. However because of that initial experience I 100% believe that ANALYZE makes a difference, particularly—as @Vince pointed out in a comment—in scenarios involving queries of large tables optimized by indexing. It's also beneficial on tables that experience considerable change via INSERT and DELETE. (Although I think VACUUM matters mostly when there is a lot of DELETE activity.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.