2

OK, I am aware that this is rather vague, but unfortunately I was not able not reproduce my problem in simplified code.

I am running a series of functions within another function (main()). All functions do something within a Postgresql 9.4 / PostGIS 2.1.7 database and work well when executed separately.

When running main() basically a couple of large tables and corresponding views with geospatial data will be dropped and recreated. The function in question will then do some polygon analysis using the following functions: st_makeline, st_touches, st_azimuth, st_length. main()uses the same connection which is being committed after each function call.

Now when I run the function from within the main function, it will take take forever (i.e. hours instead of seconds) to execute and I have no clue why that is. If I run the function right after main() has finished, it will also be slower than usual. If I wait 1-2 minutes it will be fast again. So it feels to me like some housekeeping is required. I tired (will be updated):

  • VACUUM ANALYZE of the involved tables
  • COMMIT after each step

Do you have ideas what the reason could be or how I can further debug my problem?


Update: Well, I found out how to solve my problem (see my answer below). But if anyone feels like explaining why that works, I will be happy to accept their answer.

2

Well, this is embarrassing. But maybe someone else will profit from this. I had to run

VACUUM ANALYZE 

(without specifying any table) before calling the function in question.

If somebody else cares to explain why that affects PostGIS performance, I will be happy to accept their answer.

Here is the text provided in the pgAdmin III manual:

Running VACUUM recommended

The estimated rowcount on the table "some_table" deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this table. Instead of issuing a manual VACUUM ANALYZE command on this table (you can use the pgAdmin III maintenance menu for this), running VACUUM ANALYZE on a regular or automated basis should be considered. This can be achieved using a scheduler. PostgreSQL also supplies the autovacuum daemon, which will track changes made to the database and issue vacuum commands as required automatically. In most cases, autovacuum will be the best choice.

What is VACUUM good for?

The PostgreSQL query planner makes its decision based on assumptions, taken from the estimated rowcount. If the actual rowcount is too different from the estimated rowcount, the planner might take a wrong decision, resulting in a query plan that is not optimal. This might result in poor performance. The PostgreSQL storage needs VACUUM to have transaction IDs in tables fixed. Additionally, outdated rows from updates and deletes are not cleaned up until the VACUUM command has been issued on that table. In-depth information can be found in the online documentation, just press the help button.

  • Thanks for the edit @Ena2345. Where did you find this quote? – n1000 Oct 30 '15 at 15:02
  • I have a few tables that needed to be VACCUM-ed and when you click on them in pgAdmin, you get this tip. – Piskr Nov 1 '15 at 9:13
1

What Vacuum Analyze will do is re-calculate any indexes on a table. This includes any spatial indexes. If you create a table and did not Vacuum and Analyze before running queries against it, the query will be very inefficient.

I don't know why running a generic Vacuum Analyze would help, since it is usually done either as an auto-vacuum process, or on specific tables.

From http://revenant.ca/www/postgis/workshop/indexing.html:

Vacuuming

It’s worth stressing that just creating an index is not enough to allow PostgreSQL to use it effectively. VACUUMing must be performed when ever a new index is created or after a large number of UPDATEs, INSERTs or DELETEs are issued against a table. This is important enough to the efficient running of the database that PostgreSQL provides an “autovacuum” option to perform this function automatically as tables are updated.

Autovacuum is enabled by default and will both vacuum (recover space) and analyze (update statistics) on your tables at sensible intervals determined by the level of activity. While this is essential for highly transactional databases, it is not advisable to wait for an autovacuum run after adding indices or bulk-loading data. If a large batch update is performed, you should manually run VACUUM.

Vacuuming and analyzing the database can be performed separately as needed. Issuing VACUUM command will not update the database statistics; likewise issuing an ANALYZE command will not recover unused table rows. Both commands can be run against the entire database, a single table or a single column.

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.