2

I have 2 spatial tables: Streets: linestrings of streets from a country (more than 3 million) Portals: points of adress from a country (around 18 million)

I need to move each point to the closest linestring matching 2 conditions, the query to do that is like this:

update portals pt
     set geom = (select st_closestpoint(s.streets_geom, pt.portal_geom)
                from streets s
                where pt.field1 = s.field2
                and pt.field3 = s.field3
                order by pt.portal_geom <-> s.streets_geom
                limit 1)

So far the query works well in tests but when I do it for all the data, it takes too long (several days) I have spatial index on both geometries. How can I improve the query to do it faster? I can't figure it out

EDIT: Explain of the query enter image description here

4
  • 2
    do you have indexes on pt.field1, pt.field3 and s.field2, s.field3?
    – JGH
    May 6, 2021 at 15:32
  • 2
    please add the output of explain on the query
    – Ian Turton
    May 6, 2021 at 16:36
  • @JGH No, that columns don't have indexes, should I add them? May 7, 2021 at 7:53
  • @IanTurton Edited with the explain May 7, 2021 at 7:54

1 Answer 1

1

The query plan shows that for each row to be updated in pt (p in the plan), another seq scan is done on the other table s (v in the plan), which basically means that 18M * 3M = 54M rows are scanned, which has to take some time.

Add an index on the filtering key on the 2nd table (s / v), and don't forget to run vacuum analyze on it before re-running the query

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.