on postgres 15 and postgis 3.3, with the very same dataset,
without gist index on the 420k rows table, the syntax with the left join takes 25 seconds, and without 770 ms.
so to get 5 empty lines its 30 times slower.
if I add the gist index, both syntaxes takes 770 ms...
at least, this close the discussion about the versions my project will use :-)
On Sat, Jan 7, 2023 at 8:46 PM Marc Millas <marc.millas@xxxxxxxxxx> wrote:
Hi,postgres 12, postgis 3.0I 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
