[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
Resolution:               |   Keywords:
--------------------------+---------------------------

Comment (by Algunenano):

 Implementing all four:

 Table:
 {{{
 # Select ST_GeometryType(the_geom_webmercator) as t, count(*) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 group by
 t;
     t     | count
 ----------+--------
  ST_Point | 683788
 (1 row)
 }}}



 Before:
 {{{
 EXPLAIN ANALYZE Select ST_X(the_geom_webmercator),
 ST_Y(the_geom_webmercator), ST_Z(the_geom_webmercator),
 ST_M(the_geom_webmercator) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293;
 QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..49139.60 rows=683780 width=32) (actual time=0.014..333.029
 rows=683788 loops=1)
  Planning Time: 0.070 ms
  Execution Time: 353.381 ms
 (3 rows)

 Time: 354.016 ms
 }}}

 After:
 {{{
 EXPLAIN ANALYZE Select ST_X(the_geom_webmercator),
 ST_Y(the_geom_webmercator), ST_Z(the_geom_webmercator),
 ST_M(the_geom_webmercator) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293;
 QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..49139.60 rows=683780 width=32) (actual time=0.015..228.800
 rows=683788 loops=1)
  Planning Time: 0.085 ms
  Execution Time: 249.003 ms
 (3 rows)

 Time: 249.690 ms
 }}}

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