[postgis-users] Postgres/Postgis query very slow

[Bruno Friedmann]

On 04/01/2010 11:35 AM, iKey wrote:
> 
> Hey,
> I've set up a Postgres database with Postgis support.
> Now I try to excecute the following query:
> 
> SELECT DISTINCT p.*
> FROM punkte p, lines l
> WHERE l.tags LIKE '%"highway"="footway"%'
>     AND CONTAINS(BUFFER(l.the_geom, 500), p.the_geom)
> 
> You can download the SQL's to create the tables from my server:
> http://thisisliving.info/sql.zip
> 
> It should return all datasets from the table punkte that are within a buffer
> of 500m around the datasets of the lines table.
> Anyway the query works fine, but it's veeeeery slow. It takes about 10
> minutes to get the results (1969).
> Now my question is if there is a way to speed up the query a bit.
> All help is appreciated.
> Thank you
> Eike Lüders
> 

Hi I've made some test with your data

Original select 1969 rows in 765196ms

select line toke 50ms to test the like operator

The optimized query as proposed ....

select distinct p.* from punkte p,lines l WHERE l.tags LIKE '%"highway"="footway"%'
and st_dwithin(l.the_geom,p.the_geom,500)
order by p.gid

select optimized 1969 rows 989 ms

Sure there's a significant difference....

What I've made is adding also a gist index on each table like

CREATE INDEX lines_gist
  ON lines
  USING gist
  (the_geom);

with the index the first non-optimized query is working a bit slowly 777185ms.



-- 

     Bruno Friedmann