[postgis-users] Re: X() function working for POINTs and MULTIPOINTs

[Bernhard Reimar Hoefle]

Nice hint!

But it takes approximately the twice amount of time in comparison to the
modified C function. And if you have Millions of point geometries...the double
can be a long time ;-)

ems=> explain analyze select x(geom_last) from tdtascanpoint order by
idscanpoint limit 100;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.06 rows=100 width=33) (actual time=0.033..0.568 rows=100
loops=1)
   ->  Index Scan using tdtascanpoint_pkey on tdtascanpoint 
(cost=0.00..60274.22 rows=1970979 width=33) (actual time=0.029..0.292 rows=100
loops=1)
 Total runtime: 0.771 ms
(3 rows)

ems=> explain analyze select x(geom_last,1) from tdtascanpoint order by
idscanpoint limit 100;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.06 rows=100 width=33) (actual time=0.054..0.562 rows=100
loops=1)
   ->  Index Scan using tdtascanpoint_pkey on tdtascanpoint 
(cost=0.00..60274.22 rows=1970979 width=33) (actual time=0.050..0.300 rows=100
loops=1)
 Total runtime: 0.744 ms
(3 rows)

ems=> explain analyze select x(geometryn(multi(geom_last),1)) from tdtascanpoint
order by idscanpoint limit 100;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.56 rows=100 width=33) (actual time=0.048..1.015 rows=100
loops=1)
   ->  Index Scan using tdtascanpoint_pkey on tdtascanpoint 
(cost=0.00..70129.11 rows=1970979 width=33) (actual time=0.043..0.709 rows=100
loops=1)
 Total runtime: 1.204 ms
(3 rows)


> Fluff the geometries with multi() in your query:
>
> select x(geometryn(multi(geom),1)) from geotable
>
> P.