Hibernate Spatial

PostGIS spatial indices

Details

  • Type: Improvement Improvement
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.0
  • Fix Version/s: 1.1
  • Component/s: Postgresql
  • Description:
    Hide
    Hello,

    I'm currently evaluating the HibernateSpatial project for using with a PostGIS DB and I'm facing a lack of performance when using functions like contains(geometry, geometry) : more than 60 times slower than expected because the query planner do not use the spatial indexes .

    I spent a couple of hours to understand why the difference is so huge and it seems that the PostGIS "st_*" functions are optimized and allow the spatial index usage:
    select a.name, a.topo from a, b where contains(b.topo, a.topo) ----> 5000 lines for 12~14s
    select a.name, a.topo from a, b where st_contains(b.topo, a.topo) ----> 5000 lines for 0.5~0.6s

    The main difference between the two functions simply relies on a simple && (overlap) operation between the two parameters inserted before the call to the function:
    CREATE OR REPLACE FUNCTION st_contains(geometry, geometry)
      RETURNS boolean AS
    'SELECT $1 && $2 AND _ST_Contains($1,$2)'
      LANGUAGE sql IMMUTABLE
      COST 100;

    _st_contains and contains functions are quite the same.

    -----> so the slow query modified:
     select a.name, a.topo from a, b where b.topo && a.topo AND contains(b.topo, a.topo)
    becomes a fast query (because the planner decides to use the index).

    Is it possible to integrate the support for the "st_*" PostGIS functions by (for example) mapping contains (HQL) to st_contains (SQL) or by adding the st_* HQL functions?

    Regards





    Show
    Hello, I'm currently evaluating the HibernateSpatial project for using with a PostGIS DB and I'm facing a lack of performance when using functions like contains(geometry, geometry) : more than 60 times slower than expected because the query planner do not use the spatial indexes . I spent a couple of hours to understand why the difference is so huge and it seems that the PostGIS "st_*" functions are optimized and allow the spatial index usage: select a.name, a.topo from a, b where contains(b.topo, a.topo) ----> 5000 lines for 12~14s select a.name, a.topo from a, b where st_contains(b.topo, a.topo) ----> 5000 lines for 0.5~0.6s The main difference between the two functions simply relies on a simple && (overlap) operation between the two parameters inserted before the call to the function: CREATE OR REPLACE FUNCTION st_contains(geometry, geometry)   RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Contains($1,$2)'   LANGUAGE sql IMMUTABLE   COST 100; _st_contains and contains functions are quite the same. -----> so the slow query modified:  select a.name, a.topo from a, b where b.topo && a.topo AND contains(b.topo, a.topo) becomes a fast query (because the planner decides to use the index). Is it possible to integrate the support for the "st_*" PostGIS functions by (for example) mapping contains (HQL) to st_contains (SQL) or by adding the st_* HQL functions? Regards
  • Environment:
    Java 1.6, PostGis 1.5.2

Issue Links

Activity

People

Dates

  • Created:
    15/Nov/10 5:53 PM
    Updated:
    07/May/11 7:46 PM
    Resolved:
    18/Dec/10 5:43 PM