Hi, postgres 12, postgis 3.0
I have a small table A, 11 rows with a varchar column x and a geometry column y. gist index on the geometry column. the geometry do contains multipolygons (regions on a map) I have a second table B , same structure, around 420 000 rows. no index, the geometry do contains points. all geometries are on 4326 srid. If i ask to count points in each multipolygons: select A.x, count(B.x) from A, B where st_within(B.y, A.y) group by A.x; it takes 11 seconds (everything in shared buffers). If I do the very same thing as: select A.x, count(B.x) from A left join B on st_within(B.y, A.y) group by A.x; same result, but 85 seconds (every thing in shared buffers, again) if I redo asking with explain analyze, buffers, the plan is very different. if I do create a gist index on geometry column of the big table, both syntax takes 21 seconds. I get the feeling I am missing something.. (at least 2 things...) can someone shed some light ?? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com
