PostGIS queries

[Piero Campalani]

In order to have idempotent PostGIS results, an ORDER BY clause must be added to the SQL queries.

[Dimitar Misev]

Is this a requirement in WCS? I suppose it adds a performance penalty, so it might be better to avoid it if possible.

[Piero Campalani]

Analyzing the performances on Alireza's dataset on the VM, it turns out that the query using ORDER BY in this case gets even faster since Postgres is led to adopt Index Scan on the multipoint table (being there an INDEX on the id), which is faster than the Seq Scan which would be used otherwise (Thanks Susanne).

petascopedb=# EXPLAIN ANALYZE SELECT value[1] || ',' || value[2] || ',' || value[3],
petascopedb-#         St_X(coordinate) || ' ' || St_Y(coordinate) 
petascopedb-# FROM ps9_multipoint WHERE coverage_id = <id> ORDER BY id;
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ps9_multipoint_pkey on ps9_multipoint  (cost=0.00..289817.04 rows=3014410 width=89) (actual time=1.499..20726.942 rows=3000000 loops=1)
   Filter: (coverage_id = 11)
 Total runtime: 22699.730 ms
(3 rows)

petascopedb=# EXPLAIN ANALYZE SELECT value[1] || ',' || value[2] || ',' || value[3],
petascopedb-#         St_X(coordinate) || ' ' || St_Y(coordinate) 
petascopedb-# FROM ps9_multipoint WHERE coverage_id = <id>;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on ps9_multipoint  (cost=0.00..261598.60 rows=3014410 width=85) (actual time=2075.303..22009.620 rows=3000000 loops=1)
   Filter: (coverage_id = 11)
 Total runtime: 23990.410 ms
(3 rows)

[Piero Campalani]

Fixed in changeset:760b2b9.