I wrote this part of code:

SELECT CB.id_2 AS CountyID, CA.id_2 AS NeighbourCountyID 
 FROM  "Counties10B" CB,"Counties10" CA
WHERE ST_Intersects(CA.geog,CB.geog);

But it does not use spatial index. What could be a problem? Indexes are created on both table.

enter image description here

  • I'm curious why you're calling CA.id_2 "NeighborCountyID" when your WHERE constraint does not eliminate the county from being a neighbor to itself. An ST_Touches relation would eliminate this. You should also edit the question to explain why the graphic has a "Materialize" step, and whether an spatial index exists on the materialized version of Counties10. Finally, you should report what happens when you reverse the order of the parameters (CB.geog,CA.geog). – Vince Sep 13 '15 at 16:53
  • Topology of polygons is not very good, that is the reason why I use ST_Intersects. It is not problem to remove same polygons. I realy do not know why graphic has a "Materialize" step. I tried to reverse it and i got same result. – VNenad Sep 13 '15 at 17:30
  • It's possible there is no benefit to using an index. If say 50% of your table intersects with other 50%, it won't use a spatial index. How many records do you have in each table? Also can you output (in psql) the \d "Counties10" and for "Counties10B" of your tables? Did you also run vacuum analyze "Counties10" and for the other as well? – LR1234567 Sep 13 '15 at 18:07
  • 1
    Please include the text version of explain, not the graphic. – Jakub Kania Sep 13 '15 at 22:39

Your Answer

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

Browse other questions tagged or ask your own question.