[postgis-tickets] [PostGIS] #4750: Performance issue with computed columns

[PostGIS]

#4750: Performance issue with computed columns
----------------------+---------------------------
  Reporter:  robe     |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 3.0.3
 Component:  postgis  |    Version:  3.0.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 Replying to [comment:6 Algunenano]:
 > Note that in the fast plan each parallel worker (2 of them) is doing a
 seq scan over co_random_points and then for each point (10k each) an index
 scan over over ix_co_road_line_way_gist. In the slow plan it's happening
 the other way around, each worker does a seq scan over co_road_line, and
 then for each line (666688 each) it does a seq scan over the points.
 >
 > So it looks that the thing that's making things slow is not having good
 stats and once that is solves things work as expected. Can you try to do
 the process analyzing both tables (the random points and the ones with the
 generated column) before running any query and see if those times are
 normal?

 I suspect you are right.  I thought I had analyzed both tables to confirm,
 but it's possible I missed one.  This time around, first run I rebuilt all
 the tables (didn't do any analyze what so ever) and the computed geometry
 column ran just as fast as the manual one (both around 1 sec, 356ms (give
 or take 50 ms) and both much faster than the adhoc buffer.

 That said I'm just going to close this out after I create a regress test.
 I don't think there is an issue here, but probably good to test computed
 columns in our arsenal.

 So I'll create a test that has both a computed and non-computed, a fake
 street table, analyze both tables and do a timing compare using
 clock_timestamp and throw an error if the computed timing ends up being
 slower than the adhoc buffering.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4750#comment:7>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.