Why the execution of a query is very slow using PostGis?

Question

I'm applying this Query to 8257800 point but it's taking 15 hours and it didn't return a results.

SELECT a.gid , b.gid, st_distance(a.geom,b.geom)
FROM shp1 a, shp2 b
where st_intersects(st_difference(st_buffer(a.geom,2000),st_buffer(a.geom,500)),b.geom) and abs(a.value - b.value)>400

Is there any suggestions to render this query fast?

Answer 1

Go with steps: -Create spatial indexes for your tables.

-Create Buffers

CREATE OR REPLACE VIEW b1 as 
SELECT ST_Buffer(the_geom,500) as the_geom, attributes, fid 
FROM stores;

CREATE OR REPLACE VIEW b2 as 
SELECT ST_Buffer(the_geom,2000) as the_geom, attributes, fid 
FROM stores;

(I am not sure, but maybe you should reduce here you 8 million point to less whith a materialized view like this:

select a.point_attributes
from b2 b, spoints a
where st_within(a.the_geom, b.the_geom)

)

-Create ring view r2:

CREATE OR REPLACE VIEW r2 as 
SELECT ff.fid, ff.attributes, ST_Difference(ff.the_geom, g.the_geom) as new_geom  
FROM b2 ff
JOIN b1 g ON (g.fid= ff.fid)
GROUP BY ff.fid, ff.attrubets, g.the_geom, ff.the_geom;

-Select those rings where the altitude is bigger than 400 meters.

-And the slow bit the intersection:

SELECT ff.fid, ff.attributes FROM r2 ff
JOIN points_table g ON ST_Intersects(g.geom, ff.the_geom)
GROUP BY ff.fid, ff.attributes, ff.the_geom;

Answer 2

Your query looks rather strange. What are you trying to achieve with two buffers around the same point?

You can replace ST_Distance with operator, but still we have to overthink the buffer thing.

Could you specify your goal a little?

PS: Did you build spatial index?

UPDATE:

I would try

SELECT * FROM (
    SELECT
        a.gid,
        b.gid,
        a.geom <-> b.geom distance
    FROM 
        shp1 a, shp2 b
    WHERE
        abs(a.value - b.value) > 400 AND
        ST_DWithin(a.geom, b.geom, 2000)
    ) sq
WHERE
    distance > 500;