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
    Commented Sep 13, 2015 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
    Commented Sep 13, 2015 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?
    – Regina Obe
    Commented Sep 13, 2015 at 18:07
  • 1
    Please include the text version of explain, not the graphic. Commented Sep 13, 2015 at 22:39


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.