Opened 3 years ago

Closed 3 years ago

#552 closed defect (fixed)

PostGIS queries

Reported by: pcampalani Owned by: pcampalani
Priority: major Milestone: 9.0
Component: petascope Version: development
Keywords: order by multipoint Cc: pbaumann, sebrecht
Complexity: Medium

Description

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

Change History (4)

comment:1 Changed 3 years ago by dmisev

  • Cc pbaumann added

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

comment:2 Changed 3 years ago by pcampalani

  • Cc sebrecht added

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)

comment:3 Changed 3 years ago by pcampalani

  • Summary changed from Parksmall PostGIS queries to PostGIS queries

comment:4 Changed 3 years ago by pcampalani

  • Resolution set to fixed
  • Status changed from new to closed

Fixed in changeset:760b2b9.

Note: See TracTickets for help on using tickets.