Speed up ST_X/Y/Z/M by avoiding deserialization of the point

[Algunenano]

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 togserialized_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.

[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

[Algunenano]

PR: ​https://github.com/postgis/postgis/pull/435

[Raul Marin]

In 17591:

gserialized: Style changes

References #4449

[Algunenano]

Applied in r17593