[postgis-tickets] [PostGIS] #4449: Speed up ST_X/Y/Z/M by avoiding deserialization of the point

[PostGIS]

#4449: Speed up ST_X/Y/Z/M by avoiding deserialization of the point
-------------------------+---------------------------
 Reporter:  Algunenano   |      Owner:  Algunenano
     Type:  enhancement  |     Status:  assigned
 Priority:  medium       |  Milestone:  PostGIS 3.0.0
Component:  postgis      |    Version:  trunk
 Keywords:               |
-------------------------+---------------------------
 As the subject says, we can speed up ST_X / ST_Y / ST_M and ST_Z by
 avoiding the deserialization of the points.

 I've made a quick test by using something similar
 to`gserialized_peek_gbox_p` (`gserialized_peek_first_point_x`)  and it
 seems to be have a noticeable improvement:

 Trunk:
 {{{
 # explain analyze Select ST_X(the_geom_webmercator) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293;
 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..44011.25 rows=683780 width=8) (actual time=0.017..205.250
 rows=683788 loops=1)
  Planning Time: 0.095 ms
  Execution Time: 225.928 ms
 (3 rows)
 # explain analyze Select ST_Y(the_geom_webmercator) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293;
 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..44011.25 rows=683780 width=8) (actual time=0.019..203.004
 rows=683788 loops=1)
  Planning Time: 0.096 ms
  Execution Time: 223.674 ms
 (3 rows)
 }}}


 After the change (only ST_X has been changed):
 {{{
 # explain analyze Select ST_X(the_geom_webmercator) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293;
 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..44011.25 rows=683780 width=8) (actual time=0.010..166.047
 rows=683788 loops=1)
  Planning Time: 0.081 ms
  Execution Time: 185.726 ms
 (3 rows)
 # explain analyze Select ST_Y(the_geom_webmercator) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293;
 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..44011.25 rows=683780 width=8) (actual time=0.017..202.458
 rows=683788 loops=1)
  Planning Time: 0.094 ms
  Execution Time: 222.987 ms
 }}}


 I need to think what would be the best way to expose these values
 (first_point.x, .y, .z, .m) from gserialized and add some tests.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4449>
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.