indexed tables don't seem to accelerate query performance

Question

I'm working with postgres, but the index subject seems to fit for sql as well

I have 2 tables:

table A:

name, id, geom, value, desc

where:

geom is type of geometry

geom is indexed as geom_index

table B:

 lnn_name, geom

where:

geom is type of geometry

geom is indexed as geom__lnn_index

I checked the performance of the following query:

select desc, sum((st_distance(A.geom, B.geom)<300)::INT) as res
from tblA as A, tblB as B
where A.geom is not null
group by A.desc

The time to calculate the query seems the same with or without index

Why the index don't help to reduce the time to calculate the query ?

I expect that geom will be save in a way which can reduce the query time (long distances between points will be ignored automatically)

Answer 1

Your query is saying:

1. Create a Cartesian product between the two tables.
2. One column then filters this Cartesian product based on distance.

However, the overall query is not doing any significant filtering. If you want the indexes to be used, try putting the condition in the ON clause:

select desc, count(*) as res
from tblA A join
     tblB B
     on st_distance(A.geom, B.geom) < 300
where A.geom is not null
group by A.desc;

Now the query is saying to filter the results from the join data before aggregating.