title: postGIS very slow st_buffer query - How to improve performance
body: <p>I'm using postGIS within a postgreSQL spatial database. I want to create homerange polygons from buffers around movement paths for each of 48 animals(GPS locations connected with a line). I have location data of 264'000 data points of 48 different animals.
  I do this in 3 steps to improve performance. The problem is the second step (st_buffer to create buffers around paths). If I run step 2 below for a single animal (6000 locations) it takes roughly 3 minutes. If I run the second step for a subset (one third) of the data for all animals it takes 30 minutes.
  Now that I'm running the query for all data it's been running for more than 5 hours and I'm not sure this will return successfully at all. Is there any reason why the running time would not grow more or less in a linear fashion?  Can anyone tell me if there is something wrong with my code or if one could improve on the performance of my code. Any suggestion would be most appreciated!!</p>
  
  <p>Step 1: First create a temporary table with the movement paths - Use st_simplify to simplify the paths with a float parameter of 10m</p>
  
  <pre><code>create table ws_zurich.polygon_homerange_104m_mirco_all_step1 as
  select gps.animals_original_id, st_simplify(st_makeline(gps.geom_21781),10) as geom
  from (select animals_original_id,acquisition_time, geom_21781
  from ws_zurich.mirco
  order by animals_original_id, acquisition_time) as gps
  group by gps.animals_original_id;
  
  Alter table ws_zurich.polygon_homerange_104m_mirco_all_step1
  ADD CONSTRAINT polygon_homerange_104m_mirco_step1_pkey PRIMARY KEY(animals_original_id);
  </code></pre>
  
  <p>Step 2: Create a table with the buffer around the movement paths - this is the slowest query and takes several hours for the entire data set</p>
  
  <pre><code>create table ws_zurich.polygon_homerange_104m_mirco_all_step2 as
  select animals_original_id, (st_buffer(geom, 104)) as geom
  from ws_zurich.polygon_homerange_104m_mirco_all_step1;
  </code></pre>
  
  <p>-- Step 3: use st_union to create a single polygon as home range</p>
  
  <pre><code>create table ws_zurich.polygon_homerange_104m_mirco_post_hunting as
  select row_number() over (order by animals_original_id) as id,animals_original_id,(st_union(geom)) as geom
  from ws_zurich.polygon_homerange_104m_mirco_post_hunting_step2 group by animals_original_id;
  
  Alter table ws_zurich.polygon_homerange_104m_mirco_post_hunting
  ADD CONSTRAINT polygon_homerange_104m_mirco_post_hunting_pkey PRIMARY KEY (id);
  </code></pre>