I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters from tabla_a. After that calculate fall column, group them by table_a a_id column and return highest value.
I wrote a following query that meet this criteira
SELECT DISTINCT ON (a_id) * FROM ( SELECT table_b.b_id, table_b.height - st_3ddistance(table_b.geom, table_a.geom) fall, table_b.geom, table_a.a_id FROM table_a INNER JOIN table_b ON _st_3ddwithin(table_a.geom, table_b.geom, 50)) a WHERE fall >= 0 ORDER BY a_id, fall DESC;
I added 3d geometry indexes:
CREATE INDEX table_a_geom ON table_a USING GIST (geom gist_geometry_ops_nd); CREATE INDEX table_b_geom ON table_b USING GIST (geom gist_geometry_ops_nd);
However my problem is that i can't make query to use them. Query planer is keep choosing sequence scan that is slow. I run some test changing _st_3ddwithin with st_3ddwithin, <<->> < 50 , creating 50 m buffer and intersect, st_3ddistance < 50 but everytime planner is choosing sequence scan. Is there a way to use indexes with higher performance?
My query plan:
Unique (cost=10462593.70..10473018.43 rows=1 width=144) -> Sort (cost=10462593.70..10467806.06 rows=2084945 width=144) Sort Key: table_a.nmbayuid, ((table_b.height - st_3ddistance(table_b.geomgr, table_a.geom))) DESC -> Nested Loop (cost=0.00..10243762.28 rows=2084945 width=144) Join Filter: (_st_dwithin(table_a.geom, table_b.geomgr, '50'::double precision) AND ((table_b.height - st_3ddistance(table_b.geomgr, table_a.geom)) >= '0'::double precision)) -> Seq Scan on e_veg_points_mv12404 table_b (cost=0.00..1459.47 rows=47147 width=96) -> Materialize (cost=0.00..10.97 rows=398 width=56) -> Seq Scan on e_wires_mv12404 table_a (cost=0.00..8.98 rows=398 width=56)