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