[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):

 One more observation:


 {{{
  --   1 secs 203 msec, 28446 rows
 SELECT COUNT(p.*)
     FROM test.co_random_points p
     INNER JOIN test.co_road_line r
         ON (p.way_buffer ~ r.way AND _ST_Contains(p.way_buffer,r.way) );
 }}}

 Has plan


 {{{
 Finalize Aggregate  (cost=393149116.81..393149116.82 rows=1 width=8)
 (actual time=1140.497..1163.716 rows=1 loops=1)
   ->  Gather  (cost=393149116.70..393149116.81 rows=1 width=8) (actual
 time=1139.926..1163.705 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=393148116.70..393148116.71 rows=1
 width=8) (actual time=1120.645..1120.646 rows=1 loops=2)
               ->  Nested Loop  (cost=0.29..393135032.63 rows=5233627
 width=664) (actual time=2.239..1115.969 rows=14223 loops=2)
                     ->  Parallel Seq Scan on co_random_points p
 (cost=0.00..3451.65 rows=11765 width=1232) (actual time=0.028..14.876
 rows=10000 loops=2)
                     ->  Index Scan using ix_co_road_line_way_gist on
 co_road_line r  (cost=0.29..33410.90 rows=445 width=252) (actual
 time=0.087..0.109 rows=1 loops=20000)
                           Index Cond: (way @ p.way_buffer)
                           Filter: _st_contains(p.way_buffer, way)
                           Rows Removed by Filter: 0
 Planning Time: 0.229 ms
 Execution Time: 1163.951 ms
 }}}

 In contrast:


 {{{
  --   28 secs 431 msec. count: 28446
 SELECT COUNT(p.*)
     FROM test.co_random_points p
     INNER JOIN test.co_road_line r
         ON (ST_Contains(p.way_buffer,r.way) );
 }}}

 has explain:


 {{{
 Finalize Aggregate  (cost=393107931.66..393107931.67 rows=1 width=8)
 (actual time=27923.252..27928.296 rows=1 loops=1)
   ->  Gather  (cost=393107931.54..393107931.65 rows=1 width=8) (actual
 time=27922.892..27928.290 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=393106931.54..393106931.55 rows=1
 width=8) (actual time=27903.523..27903.524 rows=1 loops=2)
               ->  Nested Loop  (cost=0.28..393096258.35 rows=4269278
 width=664) (actual time=4.112..27895.261 rows=14223 loops=2)
                     ->  Parallel Seq Scan on co_road_line r
 (cost=0.00..59761.44 rows=785044 width=252) (actual time=0.091..157.895
 rows=666688 loops=2)
                     ->  Index Scan using ix_co_random_points_way_buffer on
 co_random_points p  (cost=0.28..500.64 rows=2 width=1232) (actual
 time=0.041..0.041 rows=0 loops=1333375)
                           Index Cond: (way_buffer ~ r.way)
                           Filter: st_contains(way_buffer, r.way)
                           Rows Removed by Filter: 0
 Planning Time: 2.210 ms
 Execution Time: 27928.598 ms
 }}}

 -- manually stored (stores same as computed)


 {{{
 --  1 secs 104 msec.,count: 28446
 SELECT COUNT(p.*)
     FROM test.co_random_points p
     INNER JOIN test.co_road_line r
         ON (ST_Contains(p.way_buffer_manual,r.way) );
 }}}


 -- explain
 {{{

 Finalize Aggregate  (cost=393114999.14..393114999.15 rows=1 width=8)
 (actual time=1066.612..1090.536 rows=1 loops=1)
   ->  Gather  (cost=393114999.03..393114999.14 rows=1 width=8) (actual
 time=1066.291..1090.529 rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=393113999.03..393113999.04 rows=1
 width=8) (actual time=1047.151..1047.152 rows=1 loops=2)
               ->  Nested Loop  (cost=0.29..393103325.83 rows=4269278
 width=1768) (actual time=2.982..1042.833 rows=14223 loops=2)
                     ->  Parallel Seq Scan on co_random_points p
 (cost=0.00..8451.65 rows=11765 width=2336) (actual time=1.310..18.678
 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.080..0.101 rows=1 loops=20000)
                           Index Cond: (way @ p.way_buffer_manual)
                           Filter: st_contains(p.way_buffer_manual, way)
                           Rows Removed by Filter: 0
 Planning Time: 2.202 ms
 Execution Time: 1090.765 ms
 }}}

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