[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:  high     |  Milestone:  PostGIS 3.0.3
 Component:  postgis  |    Version:  3.0.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 okay and now some magic happened so speed is fine and fast as expected:


 {{{
 -- 1 secs 185 ms
 EXPLAIN ANALYZE SELECT COUNT(p.*)
     FROM test.co_random_points p
     INNER JOIN test.co_road_line r
         ON (ST_Contains(p.way_buffer,r.way) );
 }}}

 plan

 {{{
 Finalize Aggregate  (cost=393114999.14..393114999.15 rows=1 width=8)
 (actual time=1119.286..1142.649 rows=1 loops=1)
   ->  Gather  (cost=393114999.03..393114999.14 rows=1 width=8) (actual
 time=1118.663..1142.641 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=393113999.03..393113999.04 rows=1
 width=8) (actual time=1099.408..1099.409 rows=1 loops=2)
               ->  Nested Loop  (cost=0.29..393103325.83 rows=4269278
 width=1768) (actual time=3.032..1094.740 rows=14223 loops=2)
                     ->  Parallel Seq Scan on co_random_points p
 (cost=0.00..8451.65 rows=11765 width=2336) (actual time=1.320..19.293
 rows=10000 loops=2)
                     ->  Index Scan using ix_co_road_line_way_gist on
 co_road_line r  (cost=0.29..33410.90 rows=133 width=252) (actual
 time=0.084..0.106 rows=1 loops=20000)
                           Index Cond: (way @ p.way_buffer)
                           Filter: st_contains(p.way_buffer, way)
                           Rows Removed by Filter: 0
 Planning Time: 2.220 ms
 Execution Time: 1142.879 ms
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4750#comment:4>
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.